动态取消透视和拆分列SQL Server 2012 [英] Dynamic Unpivot and split Columns SQL Server 2012
问题描述
我有一个表作为MarketOutput，有20列
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
我想将列动态更改为行. Region和LOB是固定列，[GWP 2013]，[GWP 2014]，[LR 2013]，[LR 2014]是动态列.
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.
明年，它们将是[GWP 2015]，[LR 2015]
我想取消对这一列的透视，并将[GWP 2014]分为两列[GWP]，[2014].
I want to unpivot the column and split the [GWP 2014] as two column [GWP], [2014].
输出应该像
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
您能建议如何做到吗?
我不擅长使用SQL Server
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的格式都相同(GWP或LR，然后是空格，然后是年份，以及 -
所有列都是相同的数据类型
int或十进制，如果数据类型不相同，则应在执行unpivot之前将它们全部转换为一种数据类型，否则将得到错误.
- All the columns
[GWP 2013] , [GWP 2014] , [LR 2013] , [LR 2014] , [GWP 2015], [LR 2015], ... etcare in the same format (GWP or LR then space then the year, and All of the columns are of the same data type
intor decimal, if the data types are not the same you should cast all of them into one data type before doing theunpivototherwise you will got an error.
这将为您提供:
| 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 |
更新:
我使用 FOR XML PATH('') .. 来连接所有值列表在一个字符串中，这是SQL Server的一种解决方法. @cols的值将是字符串:[GWP 2013], [GWP 2014], ....
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], ....
如果您的字段数据类型不同，则必须像这样执行UNPVOT之前，对锚点查询中所有未透视的列进行强制转换:
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);
这篇关于动态取消透视和拆分列SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
