TSQL Pivot长列列表 [英] TSQL Pivot Long List of Columns

问题描述

我正在寻找使用数据透视功能将一列的行值转换为单独的列.该列中有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提琴

如果您发布需要转换的数据样本，那么我可以调整查询以进行演示.

If you post a sample of data that you need to transform, then I can adjust my query to demonstrate.

这篇关于TSQL Pivot长列列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！