SQLServer:列到行寻找优雅的(或任何)解决方案,将列转换为行。下面是一个示例:我有一个具有以下模式的表:[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中完成任务,您有什么建议吗?
3 回答
紫衣仙女
TA贡献1839条经验 获得超15个赞
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;
添加回答
举报
0/150
提交
取消