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

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

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

https://qiita.com/masayasu_t/items/301fa2b2c657b8cad387

# サンプル
### テーブル構成
<table>
<caption>m_student</caption>
<thead>
<tr><th>number</th><th>grade</th><th>class</th></tr>
</thead>
<tr><td>20220101</td><td>1</td><td>A</td></tr>
<tr><td>20220102</td><td>1</td><td>A</td></tr>
<tr><td>20220103</td><td>1</td><td>A</td></tr>
<tr><td colspan="3">…</td></tr>
<tr><td>20200428</td><td>3</td><td>D</td></tr>
<tr><td>20200429</td><td>3</td><td>D</td></tr>
<tr><td>20200430</td><td>3</td><td>D</td></tr>
</table>

<table>
<caption>m_score</caption>
<thead>
<tr><th>number</th><th>grade</th><th>class</th></tr>
</thead>
<tr><td>20200101</td><td>英語</td><td>21</td></tr>
<tr><td>20200101</td><td>国語</td><td>78</td></tr>
<tr><td>20200101</td><td>社会</td><td>69</td></tr>
<tr><td>20200101</td><td>数学</td><td>30</td></tr>
<tr><td>20200101</td><td>理科</td><td>85</td></tr>
<tr><td>20200102</td><td>英語</td><td>36</td></tr>
<tr><td colspan="3">…</td></tr>
</table>

## PIVOT
```sql
-- 科目を横並びに変換
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]
```
<table>
<thead>
<tr><th>学籍番号</th><th>学年</th><th>組</th><th>国語</th><th>数学</th><th>英語</th><th>社会</th><th>理科</th><th>合計点</th><th>クラス順位</th><th>学年順位</th></tr>
</thead>
<tr><td>20220101</td><td>1</td><td>A</td><td>83</td><td>65</td><td>30</td><td>57</td><td>22</td><td>257</td><td>25</td><td>98</td></tr>
<tr><td>20220102</td><td>1</td><td>A</td><td>78</td><td>30</td><td>95</td><td>35</td><td>59</td><td>297</td><td>12</td><td>53</td></tr>
<tr><td>20220103</td><td>1</td><td>A</td><td>31</td><td>65</td><td>51</td><td>34</td><td>83</td><td>264</td><td>24</td><td>88</td></tr>
<tr><td>20220104</td><td>1</td><td>A</td><td>75</td><td>81</td><td>37</td><td>75</td><td>69</td><td>337</td><td>6</td><td>21</td></tr>
<tr><td>20220105</td><td>1</td><td>A</td><td>50</td><td>54</td><td>26</td><td>78</td><td>46</td><td>254</td><td>26</td><td>100</td></tr>
<tr><td colspan="11">…</td></tr>
</table>


## XML PATH
```sql
--上位３科目の合計
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]
```

<table>
<thead>
<tr><th>学籍番号</th><th>学年</th><th>組</th><th>上位3科目点数</th><th>上位3科目</th></tr>
</thead>
<tr><td>20200101</td><td>3</td><td>A</td><td>232</td><td>理科,国語,社会</td></tr>
<tr><td>20200102</td><td>3</td><td>A</td><td>118</td><td>数学,英語,社会</td></tr>
<tr><td>20200103</td><td>3</td><td>A</td><td>178</td><td>国語,理科,英語</td></tr>
<tr><td>20200104</td><td>3</td><td>A</td><td>250</td><td>英語,数学,国語</td></tr>
<tr><td>20200105</td><td>3</td><td>A</td><td>232</td><td>理科,数学,英語</td></tr>
<tr><td colspan="5">…</td></tr>
</table>