Pirate X Pirate X - 4 months ago 21
SQL Question

Can SQL do such transformation?

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 the output like

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


I tried using PIVOT but I'm not sure if it will show the outcome as I want.

The below query is a poor attempt by me (doesn't work)-

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
MONTH()
and
DATENAME
function to extract month number and name.

Answer

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