Dynamic Unpivot And Split Columns Sql Server 2012

I have a table as MarketOutput that has 20 Columns [Region] [LOB] [GWP 2013] [GWP 2014] [LR 2013] [LR 2014] ------------------------------------------------------------- North

Solution 1:

You just need to split the columns after do the UNPIVOT something like this:

WITH Unpivoted 
AS
(
    SELECT region, lob, columns, valueFROM Regions
    UNPIVOT
    (
       columns
       FORvalueIN([GWP 2013] , [GWP 2014] ,
                    [LR 2013]  , [LR 2014] ,
                    [GWP 2015], [LR 2015])
    ) AS u
) 
SELECT 
  region, 
  lob,
  columns,
  CAST(CASEWHENvalueLIKE'GWP%'THEN REPLACE(value,'GWP ', '')
       WHENvalueLIKE'LR%'THEN REPLACE(value,'LR ', '')
  ENDASINT) ASYear,
  CASEWHENvalueLIKE'GWP%'THEN'GWP'WHENvalueLIKE'LR%'THEN'LR'ENDAS Metrics
FROM Unpivoted;

And then of course you should do it dynamically to avoid listing the columns manually and do it dynamically:

DECLARE@colsAS NVARCHAR(MAX);
DECLARE@queryAS NVARCHAR(MAX);

select@cols= STUFF((SELECTdistinct','+
                        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:

• All the columns [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.

• SQL Fiddle Demo

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|

---

Update:

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] ASDECIMAL(10,2)) AS [GWP 2013],
                         CAST([GWP 2014] ASDECIMAL(10,2)) AS [GWP 2014],
                         ... etc
                       FROM Regions
                    ) AS t
                    UNPIVOT
                    (
                       columns
                       FOR value IN('+ @cols + ')
                    ) AS u
                ) 
                SELECT 
                  region, 
                  lob,
                  columns,
                  CAST(CASEWHEN value LIKE''GWP%'' THEN REPLACE(value,''GWP '', '''')WHEN value LIKE''LR%'' THEN REPLACE(value,''LR '', '''')ENDAS INT) AS Year,
                  CASEWHEN value LIKE''GWP%'' THEN ''GWP''WHEN value LIKE''LR%'' THEN ''LR''ENDAS 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@colsCastedAS NVARCHAR(MAX);

select@colsCasted= STUFF((SELECTdistinct','+'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(CASEWHEN value LIKE''GWP%'' THEN REPLACE(value,''GWP '', '''')WHEN value LIKE''LR%'' THEN REPLACE(value,''LR '', '''')ENDAS INT) AS Year,
                      CASEWHEN value LIKE''GWP%'' THEN ''GWP''WHEN value LIKE''LR%'' THEN ''LR''ENDAS Metrics
                    FROM Unpivoted';


    EXECUTE(@query);