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