Abfrage mit Dynamische Spaltennamen

T

thecrow1304

Benutzer
Beiträge
9
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
 
Werbung:
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.
 
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 ;)
 
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?


 
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
 
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
 
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.
Zum Vergrößern anklicken....
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.

2) kann ich diese ganz leicht ermitteln und bei Bedarf ergänzen.
Zum Vergrößern anklicken....
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.
 
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...
 
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.
 
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...
Zum Vergrößern anklicken....

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. ;-)


 
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.
 
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.
 
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

 
Werbung:
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.
 

Ähnliche Themen

O
Abfrage mit Kombinationen
Antworten
7
Aufrufe
144
ukulele
ukulele
C
Wert des Datensatzes "davor" ermitteln
Antworten
4
Aufrufe
412
cat_sql
C
L
Mehrung Behälterfüllstand je Zeiteinheit
Antworten
4
Aufrufe
534
Ludwigmller
L
R
SQL >> Datum für Start & Ende Miete aus EINER Tabelle mit 2 SELECT Abfragen
Antworten
1
Aufrufe
714
MDDaniel
MDDaniel
N
Datum aus Kalenderwoche und Jahr/Monat extrahieren
Antworten
5
Aufrufe
831
Thallius
T
Zurück
Oben