寻找优雅的(或任何)解决方案将列转换为行。这是一个例子:我有一张具有以下模式的表:[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicat...
寻找将列转换为行的优雅(或任何)解决方案。
这是一个例子:我有一张具有以下模式的表:
[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 中查找的位置和如何完成它有什么建议吗?
xml 方法有个缺陷,就是无法对 >、<、& 等 xml 代码进行转义。另外,通过改写如下代码可以显著提高性能:select @colsUnpivot = stuff((select ','+quotename(C.column_name) as [text()] from information_schema.columns as C where C.table_name = 'yourtable' and C.column_name like 'Indicator%' for xml path(''), type).value('text()[1]','nvarchar(max)'), 1, 1, '')