TSQL Pivot 长列列表
我希望使用数据透视函数将列的行值转换为单独的列.该列中有 100 多个不同的值,并且对数据透视函数的for"子句中的每个值进行硬编码将非常耗时,并且从可维护性的目的来看也不好.我想知道是否有更简单的方法来解决这个问题?
I am looking to use pivot function to convert row values of a column into separate columns. There are 100+ distinct values in that column and hard-coding each and every single value in the 'for' clause of the pivot function would be very time consuming and not good from maintainability purposes. I was wondering if there is any easier way to tackle this problem?
谢谢
推荐答案
您可以在 PIVOT
中使用动态 SQL 进行此类查询.动态 SQL 将获取您要在执行时转换的项目列表,从而无需对每个项目进行硬编码:
You can use Dynamic SQL in a PIVOT
for this type of query. Dynamic SQL will get the list of the items that you want to transform on execution which prevents the need to hard-code each item:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.condition_id)
FROM t c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT memid, ' + @cols + ' from
(
select MemId, Condition_id, condition_result
from t
) x
pivot
(
sum(condition_result)
for condition_id in (' + @cols + ')
) p '
execute(@query)
参见 SQL Fiddle with Demo
如果您发布需要转换的数据样本,那么我可以调整我的查询来演示.
If you post a sample of data that you need to transform, then I can adjust my query to demonstrate.
相关文章