複数のレコードを使用して動的列のピボットクエリを作成するにはどうすればよいですか？

debugcn 投稿 Dev

Tomo

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：列とレコードを「注文する」方法は？

ロネン・アリエリー

こんにちはトモ、

テーブルを作成してデータを挿入するためのクエリができたので、簡単かつ迅速に支援できます:-)

以下のソリューションがニーズに合っているかどうかを確認してください

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が要求したようにDynamicPivotバージョンを追加する

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