时间戳

首页 > 解决方案 > 如何编写具有多条记录的动态列 PIVOT 查询？

sql-server - 如何编写具有多条记录的动态列 PIVOT 查询？

问题描述

EDIT2：我做了一个新的可用示例，进入下一步，但仍然不知道如何做一些事情。请检查。

我有下表：

CREATE TABLE [dbo].[MyTable] (
             [ID] [int] IDENTITY(1,1) NOT NULL
            ,[Name] [nvarchar](50) NULL
            ,[Quantity] [int] NULL
            ,[Period] [nvarchar](10) NULL
            )

INSERT [dbo].[MyTable] VALUES
      ('foo', 1, 'Jan'),
      ('bar', 2, 'Jan'),
      ('foo', 1, 'Jan'),
      ('kin', 1, 'Jan'),
      ('blat', 5, 'Jan'),
      ('foo', 3, 'Feb'),
      ('bar', 1, 'Feb'),
      ('kin', 2, 'Feb'),
      ('blat',4, 'Feb'),
      ('foo', 1, 'Feb'),
      ('kin', 7, 'Feb'),
      ('blat', 1, 'Feb'),
      ('foo', 3, 'Mar'),
      ('bar', 1, 'Mar'),
      ('kin', 1, 'Mar'),
      ('blat', 1, 'Mar'),
      ('bar', 1, 'Mar'),
      ('kin', 2, 'Mar'),
      ('blat', 3, 'Mar')

我想通过在查询中使用 PIVOT 来实现图片中的结果：

在此处输入图像描述

到目前为止我的查询：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Period)
  FROM (SELECT Period FROM dbo.MyTable AS p
  GROUP BY Period) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT Name, Period, Quantity
   FROM dbo.MyTable AS p
) AS j
PIVOT
(
  SUM(Quantity) FOR Period IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
EXEC sp_executesql @sql;

给我下面的结果：

在此处输入图像描述

如何添加行名（foo、bar、kin、blat）、列名 Name、SUM 等？

EDIT3：如何“排序”列和记录？

标签： sql-serverexceltsqlpivotpivot-table

解决方案

美好的一天托莫，

现在我们有了创建表和插入数据的查询，可以简单快速地帮助您:-)

请检查以下解决方案是否符合您的需求

select [Name],[Jan],[Feb],[Mar],[Jan]+[Feb]+[Mar] AS Grand_Total
from (
    SELECT [Name], [Quantity],[Period]
    FROM [MyTable]
) src
pivot
(
  SUM(Quantity)
  for Period in ([Jan],[Feb],[Mar])
) piv
GO

下一步是将其转换为动态 PIVOT，一旦我们有了静态解决方案，如果您需要动态 PIVOT，这非常简单。但首先确认静态 PIVOT 返回您需要的内容并且您确实需要动态解决方案（也许这适合您的需要照原样）

更新：使用 ROLLUP 在底部添加“总计”

;With MyCTE as(
select [Name],[Jan],[Feb],[Mar],[Jan]+[Feb]+[Mar] AS Grand_Total
from (
    SELECT [Name], [Quantity],[Period]
    FROM [MyTable]
) src
pivot
(
  SUM(Quantity)
  for Period in ([Jan],[Feb],[Mar])
) piv
)
select ISNULL([Name],'Totoal') as Name ,SUM([Jan]) [Jan],SUM([Feb]) [Feb],SUM([Mar]) [Mar],SUM([Grand_Total]) [Grand_Total]
from MyCTE
GROUP BY ROLLUP ([Name])
GO

根据 OP 的要求添加 Dynamic Pivot 版本

DECLARE 
    @ColumnsList1 AS NVARCHAR(MAX),
    @ColumnsList2 AS NVARCHAR(MAX),
    @ColumnsList3 AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX);

SET @ColumnsList1 = STUFF(
    (SELECT distinct ',' + QUOTENAME([Period]) FROM [MyTable] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT @ColumnsList1
SET @ColumnsList2 = REPLACE (@ColumnsList1, ',','+')
PRINT @ColumnsList2
SET @ColumnsList3 = (
    SELECT distinct ',SUM(' + QUOTENAME([Period]) + ') as ' + QUOTENAME([Period])
    FROM [MyTable] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @ColumnsList3

SET @query = '
;With MyCTE as(
select [Name],'+@ColumnsList1+','+@ColumnsList2+' AS Grand_Total
from (
    SELECT [Name], [Quantity],[Period]
    FROM [MyTable]
) src
pivot
(
  SUM(Quantity)
  for Period in ('+@ColumnsList1+')
) piv
)
select ISNULL([Name],''Totoal'') as Name '+@ColumnsList3+',SUM([Grand_Total]) [Grand_Total]
from MyCTE
GROUP BY ROLLUP ([Name])
'
execute(@query)
GO

推荐阅读