SQL Server:列到行 [英] SQL Server : Columns to Rows
问题描述
正在寻找将列转换为行的优雅(或任何)解决方案.
Looking for elegant (or any) solution to convert columns to rows.
这是一个示例:我有一个具有以下架构的表:
Here is an example: I have a table with the following schema:
[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]
这是我想要的结果:
[ID] [EntityId] [IndicatorName] [IndicatorValue]
结果值为:
1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'
等等..
这有意义吗?您对在哪里查看以及如何在 T-SQL 中完成它有什么建议吗?
Does this make sense? Do you have any suggestions on where to look and how to get it done in T-SQL?
推荐答案
您可以使用 UNPIVOT函数将列转换为行:
You can use the UNPIVOT function to convert the columns into rows:
select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;
请注意,您要取消透视的列的数据类型必须相同,因此您可能必须在应用取消透视之前转换数据类型.
Note, the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot.
您也可以使用 CROSS APPLY
和 UNION ALL 来转换列:
You could also use CROSS APPLY
with UNION ALL to convert the columns:
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
select 'Indicator1', Indicator1 union all
select 'Indicator2', Indicator2 union all
select 'Indicator3', Indicator3 union all
select 'Indicator4', Indicator4
) c (indicatorname, indicatorvalue);
根据您的 SQL Server 版本,您甚至可以将 CROSS APPLY 与 VALUES 子句一起使用:
Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause:
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
values
('Indicator1', Indicator1),
('Indicator2', Indicator2),
('Indicator3', Indicator3),
('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);
最后,如果您有 150 列要逆透视,并且您不想对整个查询进行硬编码,那么您可以使用动态 SQL 生成 sql 语句:
Finally, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you could generate the sql statement using dynamic SQL:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot
= stuff((select ','+quotename(C.column_name)
from information_schema.columns as C
where C.table_name = 'yourtable' and
C.column_name like 'Indicator%'
for xml path('')), 1, 1, '')
set @query
= 'select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in ('+ @colsunpivot +')
) u'
exec sp_executesql @query;
这篇关于SQL Server:列到行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!