none
Wie kann man die Distinct Werte (nur Datum dd.mm.yyyy) einer Datetime-Spalte in einem PIVOT auswerten? RRS feed

  • Frage

  • Hallo zusammen,

    ich habe eine Tabelle mit 3 Spalten 'Ort' as nvarchar, 'Nummer' as nvarchar, 'Datum' as DateTime. Wie folgt:

    CREATE TABLE [dbo].[PivotDateTable](
    	[Ort] [nvarchar](50) NULL,
    	[Nummer] [nvarchar](50) NULL,
    	[Datum] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[PivotDateTable] ([Ort], [Nummer], [Datum]) VALUES (N'A', N'1111', CAST(N'2018-07-10T12:30:31.000' AS DateTime))
    INSERT [dbo].[PivotDateTable] ([Ort], [Nummer], [Datum]) VALUES (N'B', N'1111', CAST(N'2018-08-12T11:45:01.000' AS DateTime))
    INSERT [dbo].[PivotDateTable] ([Ort], [Nummer], [Datum]) VALUES (N'A', N'2222', CAST(N'2018-08-13T08:00:59.000' AS DateTime))
    INSERT [dbo].[PivotDateTable] ([Ort], [Nummer], [Datum]) VALUES (N'C', N'1111', CAST(N'2017-09-23T13:50:45.000' AS DateTime))
    INSERT [dbo].[PivotDateTable] ([Ort], [Nummer], [Datum]) VALUES (N'B', N'2222', CAST(N'2018-09-01T16:06:00.000' AS DateTime))
    


    Die Werte der Spalte 'Datum' (nur das Datum, ohne Uhrzeit) aus bestimmten Orten (z.B. [A], [B]) sollen mit den Nummern pivotiert werden.

    Ich erwarte die Ausgabe:
    Nummer 10.07.2018 |12.08.2018 | 13.08.2018 | 01.09.2018
    1111             1                 1                 0                   0
    2222             0                 0                 1                   1

    Mit der folgenden Abfrage erhalte ich immer Fehlermeldungen, obwohl ich die Fehlermeldung in dem PRINT nicht erkennen kann:

    DECLARE @oList AS NVARCHAR(max),
    		@queryKriterium AS NVARCHAR(max),
    		@queryHitliste AS NVARCHAR(max),
    		@cols AS NVARCHAR(max)
    
    SET @oList = ';A;B;'
    SET @queryKriterium = N'
    	SELECT @cols = 
    	STUFF
    	(
    		(
    			SELECT '','' + CONVERT(VARCHAR(10), Datum, 104) AS NurDatum 
    			FROM PivotDateTable
    			WHERE ''' + @oList + ''' LIKE ''%;'' + CAST(Ort AS NVARCHAR(max)) + '';%''
    			GROUP BY Datum
    			FOR XML PATH(''''), TYPE
    		).value(''.'', ''NVARCHAR(max)''),1,1,''''
    	)
    	';
    	DECLARE @p NVARCHAR(MAX) = '@cols nvarchar(max) output';
    
    	EXECUTE sp_executesql @queryKriterium, @p, @cols = @cols OUTPUT;
    	--PRINT @cols
    	PRINT @queryKriterium
    
    SET @queryHitliste = N'
    ;WITH orte AS
    (
    	SELECT * FROM PivotDateTable
    	WHERE ''' + @oList + ''' LIKE ''%;'' + Ort + '';%''
    ),
    
    SELECT pvt.Nummer, ' + @cols + '
    FROM
    (
    	SELECT Nummer, Datum
    	FROM
    	( 
    		SELECT Nummer, Datum FROM orte
    	) as x
    ) as p
    
    PIVOT
    (
    	COUNT(Datum)
    	FOR Datum in (' + @cols + ')
    ) as pvt'
    
    
    PRINT @queryHitliste
    EXECUTE(@queryHitliste)

    Hat hier jemand eine Idee, was hier falsch ist?

    Wie kann ich abfangen, dass zu viele Datumsspalten generiert werden würden?

    Gruß

    Jürgen

    Mittwoch, 26. September 2018 11:16

Antworten

  • Natürlich wird das GroupBy(Datum) ausgeführt, allerdings auf der basis des Ursprungswertes.
    Da du nur auf dem Datum-Teil aggregieren willst, musst du den Extract-Ausdruck im Group by wiederholen, also:

    group by CONVERT(VARCHAR(10), Datum, 104)

    Oder wegen der Leserlichkeit eine derived Table verwenden:

    select NurDatum from (
    SELECT '','' + quotename(CONVERT(VARCHAR(10), Datum, 104)) AS NurDatum FROM PivotDateTable
    where ....
    ) hugo
    group by NurDatum

    Alternativ ginge auch:

    SELECT distinct '','' + quotename(CONVERT(VARCHAR(10), Datum, 104)) AS NurDatum 
    			FROM PivotDateTable
    			WHERE ''' + @oList + ''' LIKE ''%;'' + CAST(Ort AS NVARCHAR(max)) + '';%''
    			FOR XML PATH(''''), TYPE

    da du ja nur ein Ergebnisfeld benötigst.


    • Als Antwort markiert Jürgen Sch Donnerstag, 27. September 2018 13:43
    • Bearbeitet Der Suchende Donnerstag, 27. September 2018 13:47 Group By bei distinct überflüssig
    Donnerstag, 27. September 2018 12:12

Alle Antworten

  • Hallo Jürgen,

    Quotename fehlte und ein Komma vor dem SELECT nach der CTE war zuviel!

    SET @queryKriterium = N'
    	SELECT @cols = 
    	STUFF
    	(
    		(
    			SELECT '','' + quotename(CONVERT(VARCHAR(10), Datum, 104)) AS NurDatum 
    			FROM PivotDateTable
    			WHERE ''' + @oList + ''' LIKE ''%;'' + CAST(Ort AS NVARCHAR(max)) + '';%''
    			GROUP BY Datum
    			FOR XML PATH(''''), TYPE
    		).value(''.'', ''NVARCHAR(max)''),1,1,''''
    	)
    	';
    	DECLARE @p NVARCHAR(MAX) = '@cols nvarchar(max) output';
    
    	EXECUTE sp_executesql @queryKriterium, @p, @cols = @cols OUTPUT;
    	--PRINT @cols
    --	PRINT @queryKriterium
    
    
    SET @queryHitliste = N'
    ;WITH orte AS
    (
    	SELECT * FROM PivotDateTable
    	WHERE ''' + @oList + ''' LIKE ''%;'' + Ort + '';%''
    )
    SELECT pvt.Nummer, ' + @cols + '
    FROM
    (
    	SELECT Nummer, Datum
    	FROM
    	( 
    		SELECT Nummer, Datum FROM orte
    	) as x
    ) as p
    
    PIVOT
    (
    	COUNT(Datum)
    	FOR Datum in (' + @cols + ')
    ) as pvt';
    Was meinst Du mit "zu viele Datumsspalten"?


    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu


    Mittwoch, 26. September 2018 12:11
    Beantworter
  • Hallo Christoph,

    danke für deine Antwort. Das waren genau die SyntaxFehler (kam durch CopyAndPaste :-) ), nur wird die Anzahl der Nummern an den verschiedenen Tagen nicht berechnet. Da kommt überall 0 heraus.

    Mit zu vielen Datumsspalten meine ich, dass es in unserer Produktivumgebung vorkommen kann, dass es tausend und mehr Tage geben kann. Ist das in den Columns nicht limitiert?

    Gruß Jürgen

    Donnerstag, 27. September 2018 07:30
  • Ergänzung:

    Außerdem werden in der Abfrage für die @cols Tage mit gleichem Datum mehrfach ausgegeben. Dazu müsste man die Ausgangstabelle um z.B. einen Datensatz für z.B. Ort 'A' an einem bereits vorhandenen Tag, nur andere Uhrzeit ergänzen.

    Offensichtlich wird das 'GROUP BY Datum' nicht ausgeführt????

    Gruß Jürgen

    Donnerstag, 27. September 2018 08:02
  • Natürlich wird das GroupBy(Datum) ausgeführt, allerdings auf der basis des Ursprungswertes.
    Da du nur auf dem Datum-Teil aggregieren willst, musst du den Extract-Ausdruck im Group by wiederholen, also:

    group by CONVERT(VARCHAR(10), Datum, 104)

    Oder wegen der Leserlichkeit eine derived Table verwenden:

    select NurDatum from (
    SELECT '','' + quotename(CONVERT(VARCHAR(10), Datum, 104)) AS NurDatum FROM PivotDateTable
    where ....
    ) hugo
    group by NurDatum

    Alternativ ginge auch:

    SELECT distinct '','' + quotename(CONVERT(VARCHAR(10), Datum, 104)) AS NurDatum 
    			FROM PivotDateTable
    			WHERE ''' + @oList + ''' LIKE ''%;'' + CAST(Ort AS NVARCHAR(max)) + '';%''
    			FOR XML PATH(''''), TYPE

    da du ja nur ein Ergebnisfeld benötigst.


    • Als Antwort markiert Jürgen Sch Donnerstag, 27. September 2018 13:43
    • Bearbeitet Der Suchende Donnerstag, 27. September 2018 13:47 Group By bei distinct überflüssig
    Donnerstag, 27. September 2018 12:12
  • Hallo bfuerchau,

    danke für deine Antwort.

    Deinen ersten Vorschlag hatte ich schon ausprobiert, allerdings kam dann immer der Fehler "Spalte... nicht gefunden".

    Deine Alternative mit "DISTINCT' ist die kürzeste und funktioniert wunderbar.

    Danke!

    Gruß Jürgen

    Donnerstag, 27. September 2018 13:46
  • Welchen Fehler hast du denn bekommen bzw. wie sah dein SQL da aus?
    https://docs.microsoft.com/de-de/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017

    Natürlich enthält der GroupBy-Ausdruck dann kein " as xxx";-).

    Donnerstag, 27. September 2018 13:51
  • Moin bfuerchau,

    sorry, du hattest recht, es geht auch mit deiner 1. Alternative. Ich hatte da wohl offensichtl. noch einen Syntaxfehler drin.

    Ich habe da noch eine letzte Frage:
    Wenn Fehler angezeigt werden, ist im Fehlertext auch immer eine Zeilenangabe. Diese stimmt aber nie mit der tatsächlichen Zeilennummer in meinem Code überein. Wie kann man die Zeilenangaben überprüfen?

    Gruß Jürgen

    Freitag, 28. September 2018 05:01
  • Da du den SQL ja dynamisch strickst, gilt die Zeile relativ zum Stringanfang.
    • Als Antwort vorgeschlagen Der Suchende Freitag, 28. September 2018 13:26
    Freitag, 28. September 2018 06:54
  • Alles klar.

    Danke!

    Freitag, 28. September 2018 13:04