Can SQL PIVOT do such a conversion?

Question

My data looks like

+-------+-------+-------+------+ | Group | Count | Month | Year | +-------+-------+-------+------+ | A | 102 | Jan | 2015 | | B | 20 | Jan | 2016 | | C | 30 | Feb | 2015 | | A | 10 | Jan | 2016 | | C | 20 | Feb | 2016 | +-------+-------+-------+------+ 

I want a result like

 +-------+-------+------+-------+ | Group | Month | 2015 | 2016 | +-------+-------+------+-------+ | A | Jan | 102| 10 | | B | Jan | 20 | 0 | | C | Feb | 30 | 20 | +-------+-------+------+-------+ 

I tried to use PIVOT, but I'm not sure that it will show the result as I want.

The following query is a bad attempt (not working) -

 SELECT 'Total' AS Total, [2015], [2016] FROM (SELECT DATENAME(YEAR,[mydate]) as Y, Datename(month,[mydate]) as M FROM incidents) AS SourceTable PIVOT ( count(DATENAME(YEAR,[mydate])) FOR DATENAME(YEAR,[mydate]) IN (2015,2016) ) AS PivotTable; 

My date column is in this format 2016-01-20 03:00:11.000 . I use the MONTH() and DATENAME to retrieve the month number and name.

Answer 1

I think this is what you need:

 WITH Src AS ( SELECT * FROM (VALUES ('A',102, 'Jan', 2015), ('B', 20, 'Jan', 2016), ('C', 30, 'Feb', 2015), ('A', 10, 'Jan', 2016), ('C', 20, 'Feb', 2016)) T([Group], Count, Month, Year) ) SELECT [Group],Month,ISNULL([2015],0) [2015],ISNULL([2016],0) [2016] FROM Src PIVOT (SUM(Count) FOR Year IN ([2015], [2016])) AS Pvt ORDER BY [Group],Month 

Answer 2

Actually there is no need for a valid pivot operation.

 select "Group", "Month", case when "Year" = 2015 then sum("Count") end as "2015", case when "Year" = 2016 then sum("Count") end as "2016" from incidents group by "Group", "Month" 

The sum() aggregate is just a dummy function with example data. This would be as easy as min() or max() or avg() .