Convert Rows of dates into summary columns with 'Pivot' in SQL Server

Question

I'm trying to create a query to summarize data by month and year and have stumbled upon PIVOT and have been trying that (I have SQL Server 2012). I have data that looks like this:

Supplier  Date       Sales
          (mm-yyyy) 
--------  -------   ------
A         01-2012   157.54
A         02-2012   215.43
A         03-2012   993.88
B         04-2014    85.50
B         04-2014    41.50
C         01-2012   112.22
C         05-2015    84.55
C         06-2015  1188.42
C         07-2015   445.58

Basically hundreds of suppliers with a date and a sales amount. I would like to create a summary of this data by month and year in a format like this:

Supplier    Month   2012    2013    2014    2015     
--------    -----   ----    ----    ----    ----
A           01      100.51  155.96  15.10   699.66   
A           02      54.89   155.55      0   144.52 
A           03      11.53   488.99  419.98  155.21   
A           04
A           05
A           06
A           07      
A           08
A           09
A           10
A           11
A           12
B           01
B           02

Each supplier will have 12 rows, one for each month, and then the years are summarized in columns.

Can this be done with a PIVOT or is there a better way? I could do this another way that is much more painful, but based on seeing a number of PIVOT questions here, like this one: Convert Rows to columns using 'Pivot' in SQL Server, I thought it might be done easily.

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Mar 17 '16 at 21:03

score 1 · Accepted Answer · answered Mar 17 '16 at 21:13

;WITH cte AS (
SELECT * FROM (VALUES
('A', '01-2012', 157.54),
('A', '02-2012', 215.43),
('A', '03-2012', 993.88),
('B', '04-2014', 85.50),
('B', '04-2014', 41.50),
('C', '01-2012', 112.22),
('C', '05-2015', 84.55),
('C', '06-2015', 1188.42),
('C', '07-2015', 445.58)) as t(Supplier, [Date], Sales)
)

SELECT Supplier,[Month],[2012],[2013],[2014],[2015]
FROM
(
SELECT Supplier,
       LEFT([Date],2) as [Month],
       CASE WHEN RIGHT([Date],4) = 2012 THEN '2012' 
            WHEN RIGHT([Date],4) = 2013 THEN '2013' 
            WHEN RIGHT([Date],4) = 2014 THEN '2014' 
            WHEN RIGHT([Date],4) = 2015 THEN '2015' 
            ELSE NULL
            END as [Year],
       Sales
FROM cte
) d
pivot
(
SUM(Sales) for [Year] in ([2012],[2013],[2014],[2015])
) piv
ORDER BY Supplier, [Month];

Result:

Supplier    Month   2012    2013    2014    2015
A           01      157.54  NULL    NULL    NULL
A           02      215.43  NULL    NULL    NULL
A           03      993.88  NULL    NULL    NULL
B           04      NULL    NULL    127.00  NULL
C           01      112.22  NULL    NULL    NULL
C           05      NULL    NULL    NULL    84.55
C           06      NULL    NULL    NULL    1188.42
C           07      NULL    NULL    NULL    445.58

Convert Rows of dates into summary columns with 'Pivot' in SQL Server

1 Answers1