データを横並びにする（PIVOT、XML PATH）

はじめに

SQLServerで使用頻度が少なく忘れがちな横並びにするクエリ「PIVOT」と「XML PATH」のサンプルを纏めておきます。
ついでにWITH句やROW_NUMBERもサンプルに組み込みました。

横並びを縦並びにするサンプルはこちら↓↓↓

サンプル

テーブル構成

m_student

number	grade	class
20220101	1	A
20220102	1	A
20220103	1	A
…
20200428	3	D
20200429	3	D
20200430	3	D

m_score

number	grade	class
20200101	英語	21
20200101	国語	78
20200101	社会	69
20200101	数学	30
20200101	理科	85
20200102	英語	36
…

PIVOT

-- 科目を横並びに変換
SELECT T.[number] AS 学籍番号
     , [grade] AS 学年
     , [class] AS 組
     , 国語
     , 数学
     , 英語
     , 社会
     , 理科
     , [score] AS 合計点
     , ROW_NUMBER() OVER(PARTITION BY [grade], [class] ORDER BY [score] DESC) AS クラス順位
     , ROW_NUMBER() OVER(PARTITION BY [grade] ORDER BY [score] DESC) AS 学年順位
  FROM (SELECT [m_student].[number]
             , [grade]
             , [class]
             , [subject]
             , [score]
          FROM [m_student]
         INNER JOIN [m_score]
                 ON [m_score].[number] = [m_student].[number]) AS S
 PIVOT (SUM([score]) FOR [subject] IN (国語, 数学, 英語, 社会, 理科)) AS T
 INNER JOIN (SELECT [number]
                  , SUM([score]) AS [score]
               FROM [m_score]
              GROUP BY [number]) AS [m_score_sum]
         ON [m_score_sum].[number] = T.[number]
 ORDER BY [grade]
        , [class]
        , T.[number]

学籍番号	学年	組	国語	数学	英語	社会	理科	合計点	クラス順位	学年順位
20220101	1	A	83	65	30	57	22	257	25	98
20220102	1	A	78	30	95	35	59	297	12	53
20220103	1	A	31	65	51	34	83	264	24	88
20220104	1	A	75	81	37	75	69	337	6	21
20220105	1	A	50	54	26	78	46	254	26	100
…

XML PATH

--上位３科目の合計
WITH 
[m_score_rank] AS (
SELECT [number]
     , [subject]
     , [score]
     , ROW_NUMBER() OVER(PARTITION BY [number] ORDER BY [score] DESC) AS [score_rank]
  FROM [m_score]
),
[m_score_rank_sum] AS (
SELECT [number]
     , SUM([score]) AS [score_top3]
  FROM [m_score_rank]
 WHERE [score_rank] <= 3
 GROUP BY [number]
)
SELECT [m_student].[number] AS 学籍番号
     , [grade] AS 学年
     , [class] AS 組
     , [score_top3] AS 上位3科目点数
     , STUFF(
         CONVERT(
           NVARCHAR(MAX)
         , (SELECT TOP 3 ',' + [m_score].[subject]
              FROM [m_score]
             WHERE [m_score].[number] = [m_student].[number]
             ORDER BY [score] DESC
               FOR XML PATH(''), TYPE
           )
         ), 1, 1, '') AS 上位3科目
  FROM [m_student]
 INNER JOIN [m_score_rank_sum]
         ON [m_score_rank_sum].[number] = [m_student].[number]

学籍番号	学年	組	上位3科目点数	上位3科目
20200101	3	A	232	理科,国語,社会
20200102	3	A	118	数学,英語,社会
20200103	3	A	178	国語,理科,英語
20200104	3	A	250	英語,数学,国語
20200105	3	A	232	理科,数学,英語
…