如何创建数据透视查询 [英] How to create a pivot query

问题描述

想象一下我有这张桌子:

Imagine I have this table:

Column A | Column B | Column C
------------------------------
   111         X        10
   111         Y        12

如何查询该表以显示如下结果:

How can I query this table to show the results like these:

Column A |     X     |      Y
-----------------------------------
   111         10           12

推荐答案

您可以通过

You can perform this via a PIVOT. You can use either a static PIVOT where you know the number of columns that you want to rotate or you can use a dynamic PIVOT

静态数据透视(请参见带有演示的SQL提琴)

Static Pivot (see SQL Fiddle with Demo)

SELECT *
FROM 
(
  select *
  from t1
) x
pivot
(
  min(columnc)
  for columnb in ([X], [Y])
) p

动态数据透视表(请参见带演示的SQL提琴)

Dynamic Pivot (see SQL Fiddle with Demo)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(columnb) 
                    from t1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT columna, ' + @cols + ' from 
             (
                select *
                from t1
            ) x
            pivot 
            (
                min(ColumnC)
                for ColumnB in (' + @cols + ')
            ) p '

execute(@query)

两个版本将给出相同的结果.当您要转换的列数未知时，第二个方法起作用.

Both versions will give the same results. The second works when you have an unknown number of columns that will be transformed.

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