SQL Server :从列到行
SQL Server :从列到行
提问于 2013-08-03 05:13:45
寻找优雅的(或任何)解决方案来将列转换为行。
下面是一个示例:我有一个具有以下模式的表：
[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中查找和如何完成它的建议吗？
回答 4
Stack Overflow用户
回答已采纳
发布于 2013-08-03 05:20:56
您可以使用UNPIVOT函数将列转换为行：
select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;请注意，要取消透视的列的数据类型必须相同，因此您可能需要在应用取消透视之前转换数据类型。
您还可以将CROSS APPLY与UNION ALL一起使用来转换列：
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版本，您甚至可以使用带有VALUES子句的CROSS APPLY：
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
values
('Indicator1', Indicator1),
('Indicator2', Indicator2),
('Indicator3', Indicator3),
('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);最后，如果您有150列要取消透视，并且不想对整个查询进行硬编码，那么可以使用动态sql生成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;Stack Overflow用户
发布于 2017-05-17 23:05:04
为了帮助新的读者，我创建了一个例子来更好地理解@bluefeet关于UNPIVOT的答案。
SELECT id
,entityId
,indicatorname
,indicatorvalue
FROM (VALUES
(1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
(2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
(3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
(4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
indicatorvalue
FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;Stack Overflow用户
发布于 2016-03-09 02:49:23
DECLARE @TableName varchar(max)=NULL
SELECT @TableName=COALESCE(@TableName+',','')+t.TABLE_CATALOG+'.'+ t.TABLE_SCHEMA+'.'+o.Name
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name
WHERE i.indid < 2
AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0
AND i.rowcnt >350
AND o.xtype !='TF'
ORDER BY o.name ASC
print @tablename您可以获取行数大于350的表的列表。你可以在表的解决方案列表中看到行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18026236
复制相关文章
相似问题