1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Abfrage mit Dynamische Spaltennamen

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von thecrow1304, 13 Oktober 2011.

  1. thecrow1304

    thecrow1304 Benutzer

    Hallo Forum,

    ich habe mich heute hier angemeldet, weil ich hoffe ihr könnt mir weiterhelfen.

    Zum Problem:
    Ich habe 2 Tabellen. T1 und T2

    T1 sieht wie folgt aus:
    ID ANREDE
    1 Herr
    2 Frau
    3 Firma

    T2 sieht wie folgt aus
    ID ID_T1 TIMESTAMP
    1 1 2011-01-01 10:01
    2 1 2011-01-01 11:04
    3 2 2011-01-05 11:14
    4 1 2011-01-05 13:24
    5 3 2011-01-01 11:54

    Nun benötige ich als Ausgabe die Anzahl (counts) der im Zeitraum x in Tabelle T2 aufgelaufenen Anreden (für jede Anrede in Tabelle T1). Da die Daten in T1 aber dynamisch sind (es könnte ja noch weitere Anreden hinzukommen), soll die Ausgabe (Spaltennamen) dynamisch sein.

    Aussehen soll das dann so:
    Zeitraum: 2011-01-01 10:00 bis 2011-01-01 12:00

    Herr Frau Firma ...
    2 0 1

    Wie würde die Query für dieses Konstrukt aussehen, bzw. lässt sich das überhaupt (in einer Query) realisieren?

    Ich bitte um Hilfestellung.

    Danke
    Fabian
     
  2. ukulele

    ukulele Datenbank-Guru

    Also als Abfrage für einen bestimmten Zeitraum könnte ich anbieten:
    Code:
    SELECT    T1.ANREDE AS Anrede,
            count(T2.ID) AS Anzahl
    FROM    T1,
            T2
    WHERE    T1.ID = T2.ID_T1
    AND        T2.[TIMESTAMP] BETWEEN '2011-01-01 10:00:00.000' AND '2011-01-01 12:00:00.000'
    GROUP BY T1.ANREDE
    Das ist noch recht simpel, hat aber nicht dein gewünschtes Format. Um es in dein Format zu bringen müsste man folgendes tun:
    Code:
    SELECT    Zeitraum,
            sum(Herr) AS 'Herr',
            sum(Frau) AS 'Frau',
            sum(Firma) AS 'Firma'
    FROM    (    SELECT    '2011-01-01 10:00-12:00'AS Zeitraum,
                        ( CASE WHEN T1.ANREDE = 'Herr' THEN 1 ELSE 0 END ) AS 'Herr',
                        ( CASE WHEN T1.ANREDE = 'Frau' THEN 1 ELSE 0 END ) AS 'Frau',
                        ( CASE WHEN T1.ANREDE = 'Firma' THEN 1 ELSE 0 END ) AS 'Firma'
                FROM    T1,
                        T2
                WHERE    T1.ID = T2.ID_T1
                AND        T2.[TIMESTAMP]    BETWEEN    '2011-01-01 10:00:00.000'
                                        AND        '2011-01-01 12:00:00.000' ) tabelle
    GROUP BY Zeitraum
    Wenn es mehrere verschiedene Zeiträume geben soll, kommt es drauf an wie viele, ~5 kann man manuell mit einer Abfrage anlegen, bei 300 wirds nicht mehr vernünftig gehen ohne vorher Zwischenschritte zu machen. Das ist aber nicht schwer wenn man in der Lage ist, Hilfsspalten oder Hilfstabellen anzulegen und mit einem Script oder Trigger vorher zu befüllen. Die vorhandenen Zeiträume jetzt noch dynamisch anzulegen übersteigt aber meine Fähigkeiten. Könnte mit einem Cursor gehen, der läuft aber auch nicht ausschließlich im SELECT, ist also nicht immer möglich.
     
  3. ukulele

    ukulele Datenbank-Guru

    Obwohl geht doch...
    Code:
    SELECT    Zeitraum,
            sum(Herr) AS 'Herr',
            sum(Frau) AS 'Frau',
            sum(Firma) AS 'Firma'
    FROM    (    SELECT    cast(datepart(yyyy,[TIMESTAMP]) AS CHAR(4)) + '-' +
                        (    CASE
                            WHEN    datepart(mm,[TIMESTAMP]) < 10
                            THEN    '0' + cast(datepart(mm,[TIMESTAMP]) AS CHAR(1))
                            ELSE    cast(datepart(mm,[TIMESTAMP]) AS CHAR(2))
                            END ) + '-' +
                        (    CASE
                            WHEN    datepart(dd,[TIMESTAMP]) < 10
                            THEN    '0' + cast(datepart(dd,[TIMESTAMP]) AS CHAR(1))
                            ELSE    cast(datepart(dd,[TIMESTAMP]) AS CHAR(2))
                            END ) + ' ' +
                        (    CASE
                            WHEN    datepart(dd,[TIMESTAMP])/2*2 < 10
                            THEN    '0' + cast(datepart(dd,[TIMESTAMP])/2*2 AS CHAR(1))
                            ELSE    cast(datepart(dd,[TIMESTAMP])/2*2 AS CHAR(2))
                            END ) + ':00 bis ' +
                        (    CASE
                            WHEN    datepart(dd,[TIMESTAMP])/2*2+2 < 10
                            THEN    '0' + cast(datepart(dd,[TIMESTAMP])/2*2+2 AS CHAR(1))
                            ELSE    cast(datepart(dd,[TIMESTAMP])/2*2+2 AS CHAR(2))
                            END ) + ':00' AS Zeitraum,
                        ( CASE WHEN T1.ANREDE = 'Herr' THEN 1 ELSE 0 END ) AS 'Herr',
                        ( CASE WHEN T1.ANREDE = 'Frau' THEN 1 ELSE 0 END ) AS 'Frau',
                        ( CASE WHEN T1.ANREDE = 'Firma' THEN 1 ELSE 0 END ) AS 'Firma'
                FROM    T1,
                        T2
                WHERE    T1.ID = T2.ID_T1 ) tabelle
    GROUP BY Zeitraum
    War doch ganz einfach ;)
     
  4. thecrow1304

    thecrow1304 Benutzer

    Hi ukulele,

    danke für die Ausführungen.

    Jedoch scheine mir die "Dynamik" der Spaltennamen der Auswertung (

    Code:
    SELECT    Zeitraum,
    
            sum(Herr) AS 'Herr',
    
            sum(Frau) AS 'Frau',
    
            sum(Firma) AS 'Firma'...
    zu fehlen.

    Was ist, wenn eine Anrede in T1 dazukommt?


     
  5. ukulele

    ukulele Datenbank-Guru

    Das geht dann wirklich nur noch mit Cursor und wird sehr sehr kompliziert. Mir entzieht sich aber die Notwendigkeit, ich bekomme ja 1) selten neue Anreden und 2) kann ich diese ganz leicht ermitteln und bei Bedarf ergänzen.
    Code:
    SELECT    ANREDE
    FROM    T1
    GROUP BY ANREDE
    ORDER BY min(ID)
    
    oder
    
    SELECT    DISTINCT ANREDE
    FROM    T1
    
     
  6. ukulele

    ukulele Datenbank-Guru

    In diesem Beispiel würdest du die bisher auskommentierten Teile für eine Ergänzung verwenden, sobald dir eine Anrede in der Auswertung fehlt.
    Code:
    SELECT    Zeitraum,
            sum(Herr) AS 'Herr',
            sum(Frau) AS 'Frau',
            sum(Firma) AS 'Firma',
            --sum(Unbekannt) AS 'Unbekannt',
            sum(Sonstige) AS 'Sonstige`'
    FROM    (    SELECT    cast(datepart(yyyy,[TIMESTAMP]) AS CHAR(4)) + '-' +
                        (    CASE
                            WHEN    datepart(mm,[TIMESTAMP]) < 10
                            THEN    '0' + cast(datepart(mm,[TIMESTAMP]) AS CHAR(1))
                            ELSE    cast(datepart(mm,[TIMESTAMP]) AS CHAR(2))
                            END ) + '-' +
                        (    CASE
                            WHEN    datepart(dd,[TIMESTAMP]) < 10
                            THEN    '0' + cast(datepart(dd,[TIMESTAMP]) AS CHAR(1))
                            ELSE    cast(datepart(dd,[TIMESTAMP]) AS CHAR(2))
                            END ) + ' ' +
                        (    CASE
                            WHEN    datepart(dd,[TIMESTAMP])/2*2 < 10
                            THEN    '0' + cast(datepart(dd,[TIMESTAMP])/2*2 AS CHAR(1))
                            ELSE    cast(datepart(dd,[TIMESTAMP])/2*2 AS CHAR(2))
                            END ) + ':00 bis ' +
                        (    CASE
                            WHEN    datepart(dd,[TIMESTAMP])/2*2+2 < 10
                            THEN    '0' + cast(datepart(dd,[TIMESTAMP])/2*2+2 AS CHAR(1))
                            ELSE    cast(datepart(dd,[TIMESTAMP])/2*2+2 AS CHAR(2))
                            END ) + ':00' AS Zeitraum,
                        ( CASE WHEN T1.ANREDE = 'Herr' THEN 1 ELSE 0 END ) AS 'Herr',
                        ( CASE WHEN T1.ANREDE = 'Frau' THEN 1 ELSE 0 END ) AS 'Frau',
                        ( CASE WHEN T1.ANREDE = 'Firma' THEN 1 ELSE 0 END ) AS 'Firma',
                        --( CASE WHEN T1.ANREDE = 'Unbekannt' THEN 1 ELSE 0 END ) AS 'Unbekannt'
                        ( CASE WHEN T1.ANREDE NOT IN ('Herr','Frau','Firma'/*,'Unbekannt'*/) THEN 1 ELSE 0 END ) AS 'Sonstige'
                FROM    T1,
                        T2
                WHERE    T1.ID = T2.ID_T1 ) tabelle
    GROUP BY Zeitraum
     
  7. thecrow1304

    thecrow1304 Benutzer

    Die Sache mit den Anreden ist nur zur Veranschaulichung und zum vereinfachten Verständnis des Problems. Das eigentliche Problem betrifft natürlich keine Anreden und ist auch etwas komplexer.

    Das Projekt soll dem Kunden fertig übergeben werden. Hier kann und soll dann im Nachgang nicht an der Quellstruktur der Anwendung "geschraubt" werden.

    Die Sache mit dem Cursor hab ich auch aktuell in der Mache. Ich versuche das ganze in eine Stored Procedure zu fassen.
    Hier komme ich jedoch nicht weiter.

    So ist meine aktuelle Struktur in der SP:
    Code:
    ALTER PROCEDURE [dbo].[sp_ANREDEN]
                                                                               @datum_von nvarchar(20),
                                                                               @datum_bis nvarchar(20)
    AS
    
        SET dateformat dmy
        SET @datum_von  = convert(datetime,    @datum_von)
        SET @datum_bis  = convert(datetime,    @datum_bis)
    
    DECLARE T_ANREDEN CURSOR FOR SELECT ID, ANREDE FROM T1
    DECLARE @id SMALLINT
    DECLARE @anrede VARCHAR(15)
    DECALRE @sql varchar(1000)
    
    SET @sql = 'SELECT * FROM '
    
    OPEN T_ANREDEN
    WHILE 1 = 1
    BEGIN
      FETCH T_ANREDEN INTO @id, @anreden
      IF @@FETCH_STATUS < 0
    
      SET @sql = @sql + (SELECT * FROM
                             (SELECT
                                   COUNT(ID) AS @anreden
                              FROM
                                  T2
                              WHERE
                                TIMESTAMP BETWEEN @datum_von AND @datum_bis AND
                                ID_T1 = @id)
    
    END
    
    
    Nun führt die SP ja anhand des Cursors T_ANREDEN ja eine Schleife durch und wertet die Abfrage @sql mit den relevanten Parametern aus.
    Nur, wie bekomme ich ein EXEC-Statement hin, welches die kompletten SQL-String (@sql) nach durchlauf der Schleife ausführt und zurückgibt.
     
  8. ukulele

    ukulele Datenbank-Guru

    Eine SP kann glaube ich immer nur einen Wert zurück geben per RETURN. Man müsste aber während die SP läuft eine Tabelle befüllen können.

    Deine SP ist aber noch ziemlich Buggy und ihr Sinn erschließt sich mir noch nicht ganz. WHILE 1=1 wäre ja ne Endlosschleife...
     
  9. ukulele

    ukulele Datenbank-Guru

    Code:
    DECLARE    @counter INT,
            @counterID INT,
            @anrede VARCHAR(10),
            @werteliste1 VARCHAR(8000) = '',
            @werteliste2 VARCHAR(8000) = '',
            @werteliste3 VARCHAR(8000) = '',
            @abfrage VARCHAR(8000)
    
    SET        @counter = (    SELECT    count(DISTINCT ANREDE)
                            FROM    T1
                            WHERE    ANREDE IS NOT NULL
                            AND        ANREDE != 'Sonstige'
                            --weiter Ausschlusskriterien können hier eingestellt werden (Auch als Prüfung gegen Tabelle)
                            )
    
    WHILE    @counter > 0
    BEGIN
    SET        @counterID = (    SELECT    ID
                            FROM (    SELECT    TOP (@counter) ID,
                                            ROW_NUMBER() OVER (ORDER BY ID DESC) AS zeilennr
                                    FROM    T1
                                    WHERE    ANREDE IS NOT NULL
                                    AND        ANREDE != 'Sonstige'
                                    --weiter Ausschlusskriterien
                                    ORDER BY ID DESC ) tabelle
                            WHERE    tabelle.zeilennr = @counter )
            SET        @anrede = (    SELECT    ANREDE
                                FROM    T1
                                WHERE    ID = @counterID )
            SET        @werteliste1 =    @werteliste1 + ',sum(' + @anrede + ') AS ' + @anrede
            SET        @werteliste2 =    @werteliste2 + '( CASE WHEN ANREDE = ''' + @anrede +
                                    ''' THEN 1 ELSE 0 END ) AS ''' + @anrede + ''','
            SET        @werteliste3 =    @werteliste3 + '''' + @anrede + ''''
            IF        @counter > 1
            BEGIN
                    SET        @werteliste3 =    @werteliste3 + ','
            END
            SET        @counter = @counter - 1
    END
    
    SET        @abfrage =    'SELECT Zeitraum' + @werteliste1 +
                        ',sum(Sonstige) AS Sonstige FROM (
                        SELECT    cast(datepart(yyyy,[TIMESTAMP]) AS CHAR(4)) + ''-'' +
                                (    CASE
                                    WHEN    datepart(mm,[TIMESTAMP]) < 10
                                    THEN    ''0'' + cast(datepart(mm,[TIMESTAMP]) AS CHAR(1))
                                    ELSE    cast(datepart(mm,[TIMESTAMP]) AS CHAR(2))
                                    END ) + ''-'' +
                                (    CASE
                                    WHEN    datepart(dd,[TIMESTAMP]) < 10
                                    THEN    ''0'' + cast(datepart(dd,[TIMESTAMP]) AS CHAR(1))
                                    ELSE    cast(datepart(dd,[TIMESTAMP]) AS CHAR(2))
                                    END ) + '' '' +
                                (    CASE
                                    WHEN    datepart(dd,[TIMESTAMP])/2*2 < 10
                                    THEN    ''0'' + cast(datepart(dd,[TIMESTAMP])/2*2 AS CHAR(1))
                                    ELSE    cast(datepart(dd,[TIMESTAMP])/2*2 AS CHAR(2))
                                    END ) + '':00 bis '' +
                                (    CASE
                                    WHEN    datepart(hh,[TIMESTAMP])/2*2+2 < 10
                                    THEN    ''0'' + cast(datepart(hh,[TIMESTAMP])/2*2+2 AS CHAR(1))
                                    ELSE    cast(datepart(hh,[TIMESTAMP])/2*2+2 AS CHAR(2))
                                    END ) + '':00'' AS Zeitraum,' + @werteliste2 +
                        '( CASE WHEN ANREDE NOT IN (' + @werteliste3 +
                        ') THEN 1 ELSE 0 END ) AS ''Sonstige''
                        FROM T1,T2 WHERE T1.ID = T2.ID_T1 ) t GROUP BY Zeitraum'
    IF        len(@abfrage) = 8000
    BEGIN
            SELECT    'Fehler, zuviele ANREDEN.'
    END
    ELSE
    BEGIN
            EXEC (@abfrage)
    END
    Natürlich kann man das noch etwas aufräumen. Es bietet sich an, für die Berechung des Zeitraums eine SP zu erstellen und so die Anzahl der Zeichen in der Abfrage massiv zu reduzieren. VARCHAR geht nur bis 8000 und das hier mit Frau,Herr,Firma sind schon 1.343 und pro ANREDE kommen mind. 80 Zeichen dazu.
     
    Walter gefällt das.
  10. thecrow1304

    thecrow1304 Benutzer



    Ja Sorry...hab auf die Schnelle vergessen einige Sachen mit zu kopieren. WHILE 1=1 solange bis nach IF @@FETCH_STATUS < 0 ein BREAK kommt.

    Ok..werd mir deinen Script mal zu Gemüte führen.
    Danke im vorraus. ;-)


     
  11. ukulele

    ukulele Datenbank-Guru

    Ich glaub bei der Auswahl des nächsten Datensatzes in der Schleife ist mir noch ein Fehler unterlaufen. Leider fehlt mir grad etwas die Zeit es zu beheben... Teste mal mit mehr Testdatensätzen und mehr gleichen Anreden ob da nicht Spalten doppelt auftauchen.
     
  12. ukulele

    ukulele Datenbank-Guru

    Ok habs nochmal getestet, gibt nur ein Problem wenn IDs oder ANREDEN in T1 mehrfach vorkommen, was hoffentlich nicht der Fall ist. Allerdings habe ich zum Schluss bei datepart das dd einmal nicht auf hh geändert. Ich korriegers mal noch.
     
  13. thecrow1304

    thecrow1304 Benutzer

    Hi ukulele,

    ich habe den Code durchgesehen. Sieht gut aus und wird sicher auch funktionieren.
    Leider ist das Konstrukt, welches ich umzusetzen habe, doch etwas komplexer und somit sind die 8000 Zeichen die die @abfrage haben darf doch sehr schnell erreicht.

    Ich werde das "Problem" wohl doch im php angehen.

    Danke dennoch für deine Mühen und ich hoffe die Ausführungen werden anderen behilflich sein.

    Mfg

     
  14. ukulele

    ukulele Datenbank-Guru

    Wie gesagt dem kann man auch noch gut Abhilfe schaffen.

    SP:
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION    [dbo].[zr](@timestamp DATETIME)
    
    RETURNS    VARCHAR(26)
    
    AS
    
    BEGIN
            DECLARE    @char VARCHAR(26)
    
            SET        @char = cast(datepart(yyyy,@timestamp) AS CHAR(4)) + '-' +
                            (    CASE
                                WHEN    datepart(mm,@timestamp) < 10
                                THEN    '0' + cast(datepart(mm,@timestamp) AS CHAR(1))
                                ELSE    cast(datepart(mm,@timestamp) AS CHAR(2))
                                END ) + '-' +
                            (    CASE
                                WHEN    datepart(dd,@timestamp) < 10
                                THEN    '0' + cast(datepart(dd,@timestamp) AS CHAR(1))
                                ELSE    cast(datepart(dd,@timestamp) AS CHAR(2))
                                END ) + ' ' +
                            (    CASE
                                WHEN    datepart(hh,@timestamp)/2*2 < 10
                                THEN    '0' + cast(datepart(hh,@timestamp)/2*2 AS CHAR(1))
                                ELSE    cast(datepart(hh,@timestamp)/2*2 AS CHAR(2))
                                END ) + ':00 bis ' +
                            (    CASE
                                WHEN    datepart(hh,@timestamp)/2*2+2 < 10
                                THEN    '0' + cast(datepart(hh,@timestamp)/2*2+2 AS CHAR(1))
                                ELSE    cast(datepart(hh,@timestamp)/2*2+2 AS CHAR(2))
                                END ) + ':00'
    
    RETURN    @char
    END
    Script:
    Code:
    DECLARE    @counter INT,
            @counterID INT,
            @anrede VARCHAR(10),
            @werteliste1 VARCHAR(8000) = '',
            @werteliste2 VARCHAR(8000) = '',
            @werteliste3 VARCHAR(8000) = '',
            @abfrage VARCHAR(8000)
    
    SET        @counter = (    SELECT    count(DISTINCT ANREDE)
                            FROM    T1
                            WHERE    ANREDE IS NOT NULL
                            AND        ANREDE != 'Sonstige'
                            --weiter Ausschlusskriterien können hier eingestellt werden (Auch als Prüfung gegen Tabelle)
                            )
    
    WHILE    @counter > 0
    BEGIN
    SET        @counterID = (    SELECT    ID
                            FROM (    SELECT    TOP (@counter) ID,
                                            ROW_NUMBER() OVER (ORDER BY ID DESC) AS zeilennr
                                    FROM    T1
                                    WHERE    ANREDE IS NOT NULL
                                    AND    ANREDE != 'Sonstige'
                                    --weiter Ausschlusskriterien
                                    ORDER BY ID DESC ) tabelle
                            WHERE    tabelle.zeilennr = @counter )
            SET        @anrede = (    SELECT    ANREDE
                                FROM    T1
                                WHERE    ID = @counterID )
            SET        @werteliste1 =    @werteliste1 + ',sum(' + @anrede + ')AS ' + @anrede
            SET        @werteliste2 =    @werteliste2 + '(CASE WHEN ANREDE = ''' + @anrede +
                                    ''' THEN 1 ELSE 0 END)AS ''' + @anrede + ''','
            SET        @werteliste3 =    @werteliste3 + '''' + @anrede + ''''
            IF        @counter > 1
            BEGIN
                    SET        @werteliste3 = @werteliste3 + ','
            END
            SET        @counter = @counter - 1
    END
    
    SET        @abfrage =    'SELECT Zeitraum' + @werteliste1 +
                        ',sum(Sonstige)AS Sonstige FROM(
                        SELECT[dbo].[zr]([TIMESTAMP])AS Zeitraum,' + @werteliste2 +
                        '(CASE WHEN ANREDE NOT IN (' + @werteliste3 +
                        ')THEN 1 ELSE 0 END)AS''Sonstige''
                        FROM T1,T2 WHERE T1.ID = T2.ID_T1) t GROUP BY Zeitraum'
    
    IF        len(@abfrage) = 8000
    BEGIN
            SELECT    'Fehler, zuviele ANREDEN.'
    END
    ELSE
    BEGIN
            EXEC (@abfrage)
    END
    Das sollte auch der Geschwindigkeit zu gute kommen und wir haben nur noch 695 Zeichen (meine Ausgabe hat auch schon 7 Spalten) , ohne die Sonstige Spalte kann man auch ganz auf @werteliste3 verzichten, das wären nochmal bedeutend weniger Zeichen.
     
Die Seite wird geladen...

Diese Seite empfehlen