[SQL Server]SQL Server数据库中如何使用pivot函数将行转换成列(行列转换)？

问题描述

SQL Server数据库中，在做某些场景下需要将数据行列转换进行查询，使用SQL语句应该如何实现呢？

方案一

如何是SQL Server 2005及以上版本，则可使用PIVOT函数进行行列的转换，如下：

首先创建一个示例数据表并写入示例数据

CREATE TABLE #yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO #yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

如果列是已知的，则可以硬编码SQL语句，如下：

select *
from 
(
  select store, week, xCount
  from yt
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

如果列是未知的(动态的)，则可以使用如下SQL语句生成动态的行列转换查询：

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

方案二

一个类似的基于pivot函数的行列转换的实现，如下：

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

-- 获取需要转换的不重复的列名
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #TempTable) AS Weeks

-- 拼接pivot语句
SET @DynamicPivotQuery = 
  N'SELECT Store, ' + @ColumnName + ' 
    FROM #TempTable
    PIVOT(SUM(xCount) 
          FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--执行动态pivot查询语句
EXEC sp_executesql @DynamicPivotQuery