Transpose select data into column name and sum up using different criteria-MS Sql

Question

I'm working on this report where I have to sum up and transpose the data from this temp table I built after several queries. the table data looks like this http://sqlfiddle.com/#!6/570b1/1

What I'd like to do is, bring the months as columns, i.e. I would add 12 other columns from the query result like below

Dec-2015, Jan-2016..... Dec-2016. basically 12 months ahead. also, under each month, it will show the sum of the forecast column (just for that month only). and at the end, a sum for the whole year for each ID.

Some guidance in achieving this is appreciated.

Thanks.

Hi Felix, My expected result should look like this http://sqlfiddle.com/#!6/b2c0b/1 Basically, I'm summing up the forecast column for each month and showing it in the tabular (calendar) view. — Eclipse, Dec 23 '15 at 15:04
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Tab Alleman, Dec 29 '15 at 16:21

score 0 · Answer 1 · answered Dec 23 '15 at 20:09

I have come up with a solution for this query but not sure if this is the most efficient way. Also, my months and years are static. Which makes this query not very useful. Please see the query below and pls send me your comments.

SELECT Description, ComponentDescription,
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Dec2015' THEN Forecast ELSE 0 END ) AS 'Dec2015',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Jan2016' THEN Forecast ELSE 0 END ) AS 'Jan2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Feb2016' THEN Forecast ELSE 0 END ) AS 'Feb2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Mar2016' THEN Forecast ELSE 0 END ) AS 'Mar2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Apr2016' THEN Forecast ELSE 0 END ) AS 'Apr2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'May2016' THEN Forecast ELSE 0 END ) AS 'May2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Jun2016' THEN Forecast ELSE 0 END ) AS 'Jun2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Jul2016' THEN Forecast ELSE 0 END ) AS 'Jul2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Aug2016' THEN Forecast ELSE 0 END ) AS 'Aug2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Sep2016' THEN Forecast ELSE 0 END ) AS 'Sep2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Oct2016' THEN Forecast ELSE 0 END ) AS 'Oct2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Nov2016' THEN Forecast ELSE 0 END ) AS 'Nov2016',
SUM(CASE WHEN FMonth+CAST(YearOrder AS NVARCHAR(10)) = 'Dec2016' THEN Forecast ELSE 0 END ) AS 'Dec2016'

FROM (SELECT Description, ComponentDescription, FMonth, Forecast, YearOrder FROM Table1 ) AS NormalizedData
GROUP BY Description, ComponentDescription
ORDER BY Description, ComponentDescription;

Transpose select data into column name and sum up using different criteria-MS Sql

1 Answers1