multiples - Dynamic Unpivot y Columnas divididas SQL Server 2012

pivot y unpivot sql server

Question

Tengo una tabla como MarketOutput que tiene 20 columnas

[Region] [LOB]  [GWP 2013]  [GWP 2014]  [LR 2013]   [LR 2014]
-------------------------------------------------------------
North  Workers  38902.50     37,972,404   89             82

Me gustaría cambiar dinámicamente columna a filas. Región y LOB son columnas fijas, [GWP 2013], [GWP 2014], [LR 2013], [LR 2014] son ​​columnas dinámicas.

Para el próximo año serán [GWP 2015] , [LR 2015]

Quiero desvincular la columna y dividir el [GWP 2014] como dos columnas [GWP], [2014].

La salida debería ser como

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

¿Puedes sugerir cómo se puede hacer?

Soy nuevo para pivotar en SQL Server

También me gustaría insertar el resultado en una nueva tabla cada vez con la lista dinámica

Answer 1

Solo necesitas dividir las columnas después de hacer el UNPIVOT siguiente manera:

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;

Y luego, por supuesto, debe hacerlo de forma dinámica para evitar enumerar las columnas manualmente y hacerlo de forma dinámica:

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);

Esto debería funcionar bien suponiendo que:

• Todas las columnas [GWP 2013] , [GWP 2014] , [LR 2013] , [LR 2014] , [GWP 2015], [LR 2015], ... etc están en el mismo formato (GWP o LR luego espacio que el año, y

• Todas las columnas son del mismo tipo de datos int o decimal, si los tipos de datos no son los mismos, debe convertirlos a todos en un tipo de datos antes de realizar la unpivot contrario, se producirá un error.

• Demostración de SQL Fiddle

Esto te dará:

| 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 |

Actualizar:

Usé FOR XML PATH('''') .. para concatenar toda la lista de valores en una cadena, es un trabajo en SQL Server para hacer eso. El valor de @cols será la cadena: [GWP 2013], [GWP 2014], ...

Si el tipo de datos de su campo es diferente, debe hacer el molde de todas las columnas que serán desvinculadas en la consulta del delimitador antes de hacer el UNPVOT esta manera:

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'';

Si le resultó difícil escribir el elenco para todas las columnas manualmente, puede generarlo dinámicamente y anexarlo, por ejemplo:

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, '''');

Luego, en la consulta dinámica, agregue ese valor:

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);