In below screenshot you see my source and desired output table.
I cannot hardcode the columns "Product", "Year" or "Measure" as they can grow dynamically. Columns "1", "2" and so on represent a month, which can be hardcoded.
I would be happy if you could show me a dynamic query or point me to a similar solution.
Here is a Fiddle of the source.
Thank you.
Try the following:
--Data
DROP TABLE IF EXISTS TEMP
DROP TABLE IF EXISTS test
CREATE TABLE test
([Product] varchar(5), [Year] varchar(4),[Measure] varchar(10),
[1] int, [2] int, [3] int, [4] int,[5] int, [6] int,[7] int, [8] int,[9] int, [10] int,[11] int, [12] int)
;
INSERT INTO test
([Product], [Year], [Measure], [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
VALUES
('ABCD', '2017','efgh', 1, 11,21,31,41,51,61,71,81,91,100,110),
('ABCD', '2017','efgh', 2, 12,21,31,41,51,61,71,81,91,100,110),
('ABCD', '2018','mnop', 3, 13,21,31,41,51,61,71,81,91,100,110),
('ABCD', '2018','mnop', 4, 14,21,31,41,51,61,71,81,91,100,110),
('WXYZ', '2017','efgh', 5, 15,21,31,41,51,61,71,81,91,100,110),
('WXYZ', '2017','efgh', 6, 16,21,31,41,51,61,71,81,91,100,110),
('WXYZ', '2018','mnop', 7, 17,21,31,41,51,61,71,81,91,100,110),
('WXYZ', '2018','mnop', 8, 18,21,31,41,51,61,71,81,91,100,110)
;
--UNPIVOTING MONTHS (As fixed months that is why using non-dynamic unpivot)
DROP TABLE IF EXISTS TEMP
SELECT Product, [Year], Measure, [Month], [Value]
INTO TEMP
FROM
(
SELECT Product, [Year], Measure
,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM test
) AS t
UNPIVOT
(
[Value] FOR [Month] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS up;
select * from test
--Dynamic Pivoting
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
(
SELECT DISTINCT
','+ Measure
FROM TEMP c FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
print @cols
SET @query = 'SELECT Product, Year, [Month], '+@cols+' from
(SELECT
Product,
Year,
[Month],
[Value],
Measure AS Category
FROM TEMP
)x
pivot
(
sum([Value]) for Category in ('+@cols+')
) p
order by 1,2, convert(int, [Month])';
print @query
EXECUTE (@query);
DROP TABLE IF EXISTS TEMP
DROP TABLE IF EXISTS test
