如何在Transact/SQL中创建数据透视表? [英] How to create a PivotTable in Transact/SQL?

问题描述

我的源数据表是

MemID Condition_ID Condtion_Result
----------------------------------
1     C1           0
1     C2           0
1     C3           0
1     C4           1
2     C1           0
2     C2           0
2     C3           0
2     C4           0

我想要创建的预期视图是....

The expected view I want to create is ....

MemID C1 C2 C3 C4
------------------
1     1  0  0  1
2     0  0  0  1

这是另一个条件. 在上面的源表示例中，给定的MemID仅4行.此数字将根据实际情况而有所不同.我的数据透视表(或其他解决方案)应选择任意数量的条件结果并将其显示为列. 怎么做?

Here is the other condition. In the above source table example , only 4 rows for a given MemID. This number will vary in the actual situation. My pivot table(or any other solution) should pick it any number of condition results and display them as columns. How to do it ?

推荐答案

您需要使用

You need to use a PIVOT. You can use either a STATIC PIVOT where you know the values of the columns to transform or a DYNAMIC PIVOT where the columns are unknown until execution time.

静态数据透视(请参见带演示的SQL Fiddle ) :

select *
from 
(
    select memid, Condition_id, Condition_Result
    from t
) x
pivot
(
    sum(condition_result)
    for condition_id in ([C1], [C2], [C3], [C4])
) p

动态枢轴(请参见带有演示的SQL提琴) :

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)

两者都会产生相同的结果.

Both will generate the same results.

这篇关于如何在Transact/SQL中创建数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！