SQL server-Pivot-Herausforderung

Es gibt zwei gegebene Spalten – Präfix und datetime, zB

PREFIX | DATETIME ABC | 2010-01-28 07:11:31.000 DEF | 2010-02-15 07:11:31.000 DEF | 2010-01-25 07:11:31.000 

Ich möchte die data nach Jahr / Monat und Präfix pivotieren. Das Ergebnis sollte so aussehen:

 YEAR | MONTH | ABC | DEF | 2010 | Jan | 1 | 1 | 2010 | Feb | 0 | 1 | 

Mit anderen Worten, zählen, wie viel objecte mit dem gleichen Präfix den gleichen timeraum (Jahr / Monat) haben.

Ich habe diesen Code ausprobiert, aber es funktioniert nicht und ich weiß nicht wo ist das Problem.

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(O.OU01) from Test.dbo.Organisation as O group by O.OU01 order by O.OU01 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT [Year], [month], ' + @cols + ' from ( SELECT Year(A.Stand) Year, dataame(month, A.Stand) [month], O.OU01 FROM Test.dbo.Assets as A join Test.dbo.Organisation as O on A.Key = O.Key ) x pivot ( Count([O.OU01]) for O.OU01 in (' + @cols + ') ) p ' execute(@query) 

Verwenden Sie Conditional Aggregate

 SELECT Year([DATETIME]) [Year], dataame(month, [DATETIME]) [month], Count(CASE WHEN PREFIX = 'ABC' THEN 1 END) ABC, Count(CASE WHEN PREFIX = 'DEF' THEN 1 END) DEF FROM Tablename GROUP BY Year([DATETIME]), dataame(month, [DATETIME]) 

Oder mit Pivot

 SELECT * FROM (SELECT Year([DATETIME]) [Year], dataame(month, [DATETIME]) [month], PREFIX, [DATETIME] FROM tablename) a PIVOT (Count([DATETIME]) FOR PREFIX IN ([ABC], [DEF])) AS P 

SQLFIDDLE DEMO

Dynamische Version:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(PREFIX) from es group by PREFIX order by PREFIX FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT [Year], [month], ' + @cols + ' from ( SELECT Year(DATETIMEs) Year, dataame(month, DATETIMEs) [month], PREFIX FROM es ) x pivot ( Count([PREFIX]) for PREFIX in (' + @cols + ') ) p ' execute(@query) 

Demo

Verwenden Sie die PIVOT TABLE von SQL server

Versuche dies:

 SELECT Year, MonthName, [ABC], [DEF] FROM (SELECT YEAR(A.DATETIME) AS Year, DATENAME(A.DATETIME,Getdate()) AS MonthName, A.PREFIX AS Header, A.DATETIME DATA FROM tableA A ) AS A PIVOT(COUNT(DATA) FOR Header IN ([ABC], [DEF])) AS P