I have a table as MarketOutput that has 20 Columns
[Region] [LOB] [GWP 2013] [GWP 2014] [LR 2013] [LR 2014]
-------------------------------------------------------------
North Workers 38902.50 37,972,404 89 82
I would like to dynamically change column to rows. Region and LOB are fixed columns, [GWP 2013], [GWP 2014], [LR 2013], [LR 2014] are dynamic columns.
For next year they will be [GWP 2015], [LR 2015]
I want to unpivot the column and split the [GWP 2014] as two column [GWP], [2014].
The output should be like
Region [LOB] [Metrics] [Year] [Value]
--------------------------------------------------
North Workers GWP 2013 38902.50
North Workers GWP 2014 37,972,404
North Workers LR 2013 89
North Workers LR 2014 82
Can you please suggest how it can be done?
I am new to pivoting in SQL Server
I would also like to insert the output into a new table every time with dynamic list
You just need to split the columns after do the UNPIVOT something like this:
WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM Regions
UNPIVOT
(
columns
FOR value IN([GWP 2013] , [GWP 2014] ,
[LR 2013] , [LR 2014] ,
[GWP 2015], [LR 2015])
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE 'GWP%' THEN REPLACE(value,'GWP ', '')
WHEN value LIKE 'LR%' THEN REPLACE(value,'LR ', '')
END AS INT) AS Year,
CASE WHEN value LIKE 'GWP%' THEN 'GWP'
WHEN value LIKE 'LR%' THEN 'LR'
END AS Metrics
FROM Unpivoted;
And then of course you should do it dynamically to avoid listing the columns manually and do it dynamically:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(column_name)
FROM information_schema.columns
WHERE table_name = 'Regions'
AND COLUMN_NAME <> 'Region'
AND COLUMN_NAME <> 'LOB'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
SELECT @query = 'WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM Regions
UNPIVOT
(
columns
FOR value IN('+ @cols + ')
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE ''GWP%'' THEN REPLACE(value,''GWP '', '''')
WHEN value LIKE ''LR%'' THEN REPLACE(value,''LR '', '''')
END AS INT) AS Year,
CASE WHEN value LIKE ''GWP%'' THEN ''GWP''
WHEN value LIKE ''LR%'' THEN ''LR''
END AS Metrics
FROM Unpivoted';
EXECUTE(@query);
This should work fine assuming that:
[GWP 2013] , [GWP 2014] , [LR 2013] , [LR 2014] , [GWP 2015], [LR 2015], ... etc are in the same format (GWP or LR then space then the year, and All of the columns are of the same data type int or decimal, if the data types are not the same you should cast all of them into one data type before doing the unpivot otherwise you will got an error.
This will give you:
| region | lob | columns | Year | Metrics |
|--------|---------|----------|------|---------|
| North | Workers | 38902.5 | 2013 | GWP |
| North | Workers | 37972404 | 2014 | GWP |
| North | Workers | 70 | 2015 | GWP |
| North | Workers | 89 | 2013 | LR |
| North | Workers | 82 | 2014 | LR |
| North | Workers | 80 | 2015 | LR |
I used FOR XML PATH('') .. to concatenate all the list of values in one string, it is a work around in SQL Server to do that. The value of @cols will be the string: [GWP 2013], [GWP 2014], ....
If your fields data type are different, you have to do the cast of all the columns that will be unpivoted in the anchor query before doing the UNPVOT like this:
SELECT @query = 'WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM
(
SELECT
region,
lob,
CAST([GWP 2013] AS DECIMAL(10,2)) AS [GWP 2013],
CAST([GWP 2014] AS DECIMAL(10,2)) AS [GWP 2014],
... etc
FROM Regions
) AS t
UNPIVOT
(
columns
FOR value IN('+ @cols + ')
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE ''GWP%'' THEN REPLACE(value,''GWP '', '''')
WHEN value LIKE ''LR%'' THEN REPLACE(value,''LR '', '''')
END AS INT) AS Year,
CASE WHEN value LIKE ''GWP%'' THEN ''GWP''
WHEN value LIKE ''LR%'' THEN ''LR''
END AS Metrics
FROM Unpivoted';
If you found it hard to write the cast for all the columns manually, you can generate it dynamically and append it instead, for example:
DECLARE @colsCasted AS NVARCHAR(MAX);
select @colsCasted = STUFF((SELECT distinct ',' +
'CAST(' + QUOTENAME(column_name) + 'AS DECIMAL(10,2)) AS ' + QUOTENAME(column_name)
FROM information_schema.columns
WHERE table_name = 'Regions'
AND COLUMN_NAME <> 'Region'
AND COLUMN_NAME <> 'LOB'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
Then in the dynamic query append that value to it:
SELECT @query = 'WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM
(
SELECT region, lob,
' + @colsCasted + '
FROM Regions
) AS t
UNPIVOT
(
columns
FOR value IN('+ @cols + ')
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE ''GWP%'' THEN REPLACE(value,''GWP '', '''')
WHEN value LIKE ''LR%'' THEN REPLACE(value,''LR '', '''')
END AS INT) AS Year,
CASE WHEN value LIKE ''GWP%'' THEN ''GWP''
WHEN value LIKE ''LR%'' THEN ''LR''
END AS Metrics
FROM Unpivoted';
EXECUTE(@query);