如何在 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 ?

推荐答案

您需要使用 PIVOT.您可以使用 STATIC PIVOT(您知道要转换的列的值)或 DYNAMIC PIVOT(其中列在执行时间之前是未知的).

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 with Demo):

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 Fiddle with Demo):

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屋！