[解決済み】SQL Serverで「Pivot」を使って行を列に変換する。

質問

MSのピボットテーブルに関する資料を読みましたが、まだ正しく動作しないので困っています。

1列目は店舗番号、2列目は週番号、3列目はある種の合計であるとします。また、週番号は動的で、店番号は静的です。

Store      Week     xCount
-------    ----     ------
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

このように、ピボットテーブルとして出てくるようにしたいのですが。

Store        1          2          3        4        5        6....
----- 
101        138        282        220
102         96        212        123
105         37        
109

横に数字、上に週を格納する。

解決方法は？

SQL Server 2005+を使用している場合、以下のように PIVOT 関数を使用して、データを行から列に変換することができます。

週数が不明な場合は動的SQLを使用する必要があるようですが、最初はハードコードされたバージョンを使用して正しいコードを確認する方が簡単です。

まず最初に、簡単にテーブルの定義と使用するデータを紹介します。

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);

値が既知の場合は、クエリをハードコードすることになります。

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);

参照 SQLデモ .

動的バージョンでは week の数値は、カラムに変換されるべきものです。どちらも同じ結果になります。

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |