Hi,
It's quite simple. You can do this with
UNPIVOT
.
Query:
SELECT [Month],
Result
FROM
(select
sum (case when [Month] = 1 then forecastdemand else 0.0 end ) January,
sum(case when [Month] = 2 then forecastdemand else 0.0 end) February,
sum(case when [Month] = 3 then forecastdemand else 0.0 end) March ,
sum(case when [Month] = 4 then forecastdemand else 0.0 end) April ,
sum(case when [Month] = 5 then forecastdemand else 0.0 end) May ,
sum(case when [Month] = 6 then forecastdemand else 0.0 end) June ,
sum(case when [Month] = 7 then forecastdemand else 0.0 end) July ,
sum(case when [Month] = 8 then forecastdemand else 0.0 end) August ,
sum(case when [Month] = 9 then forecastdemand else 0.0 end) September ,
sum(case when [Month] = 10 then forecastdemand else 0.0 end) October ,
sum(case when [Month] = 11 then forecastdemand else 0.0 end) November ,
sum(case when [Month] = 12 then forecastdemand else 0.0 end) December
from forecastreorder as result
where Productid = 2) AS SourceTable
UNPIVOT (Result
FOR [Month] IN ([January],
[February],
[March],
[April],
[May],
[June],
[July],
[August],
[September],
[October],
[November],
[December])) AS UnpivotTable;
Result:
Month Result
January 500.0
February 200.0
March 300.0
April 400.0
May 0.0
June 0.0
July 0.0
August 0.0
September 0.0
October 0.0
November 0.0
December 0.0
You can eliminate rows with [Result] = 0 by adding
WHERE Result > 0
.