![[PukiWiki]](image/logo.png "[PukiWiki]"){kind=logo}
#author("2023-05-01T17:48:50+09:00","","") #author("2023-05-01T17:49:38+09:00","","") #navi(../) * Microsoft SQL ServerでPIVOTを使ったサンプル [#d8904610] 本資料は Microsoft SQL Server で PIVOT を使った記事となります。~ 例えば、集計しGROUP BY した結果を横にしたい…縦にしたい…など集計結果の表を縦横に変えたい場合にPIVOTが便利かもしれません。~ CASE WHENを使えば、縦横表示は可能ですが…~ #contents * 動作確認環境 [#kd6d3f14] SELECT @@VERSION Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19045: ) * PIVOTを試す前に [#b94b29d6] 以下に記すテーブルとレコードをINSERTし、販売者(person)と商品(product)でPIVOTしてみます。~ また、意図しない動作になる例も記します。 ** テストテーブルおよびテストデータ [#he178148] 本記事の動作を試すには以下のテーブル作成とテストデータをINSERTしてください。 -テーブル CREATE TABLE sales ( sale_date date ,person varchar(30) ,product varchar(30) ,quantity int ); -テストデータ INSERT INTO sales VALUES ('2023-05-01','佐藤','Linux',1) ,('2023-05-01','佐藤','macOS',2) ,('2023-05-01','佐藤','Windows',3) ,('2023-05-02','佐藤','Linux',4) ,('2023-05-02','佐藤','macOS',5) ,('2023-05-02','佐藤','Windows',6) ,('2023-05-01','鈴木','Linux',10) ,('2023-05-01','鈴木','macOS',20) ,('2023-05-01','鈴木','Windows',30) ,('2023-05-02','鈴木','Linux',40) ,('2023-05-02','鈴木','macOS',50) ,('2023-05-02','鈴木','Windows',60) ,('2023-05-01','高橋','Linux',100) ,('2023-05-01','高橋','macOS',200) ,('2023-05-01','高橋','Windows',300) ,('2023-05-02','高橋','Linux',400) ,('2023-05-02','高橋','macOS',500) ,('2023-05-02','高橋','Windows',600); ** GROUP BY した場合 [#rc9b0994] 本記事では GROUP BY し数量(quantity)の合計値(SUM)を販売者(person)と商品(product)をPIVOTを使って、カラムとして表示します。~ 以下のようにGROUP BY を使用すると以下のようになります。~ -SQL SELECT person ,product ,SUM(quantity) AS total FROM sales GROUP BY person ,product ORDER BY person ,product -結果 |person|product|total|h |高橋|Linux|500| |高橋|macOS|700| |高橋|Windows|900| |佐藤|Linux|5| |佐藤|macOS|7| |佐藤|Windows|9| |鈴木|Linux|50| |鈴木|macOS|70| |鈴木|Windows|90| * PIVOTを試す [#q6c8f1f7] personとproductでPIVOTしてみます。 ** 販売者(person)でPIVOT [#e438114b] 販売者(person)でPIVOTした時のサンプルSQLと結果となります。 ***SQL [#m51048fa] SELECT pvt.product ,pvt.高橋 ,pvt.佐藤 ,pvt.鈴木 FROM ( SELECT person, product, quantity FROM sales ) T PIVOT ( SUM(quantity) FOR person IN (高橋,佐藤,鈴木) ) AS pvt ***結果 [#l9dfc33d] |product|高橋|佐藤|鈴木|h |Linux|500|5|50| |macOS|700|7|70| |Windows|900|9|90| 販売者(person)がカラムになっているのが確認できます。 ** 商品(product)でPIVOT [#z9c45f5c] 商品(product)でPIVOTした時のサンプルSQLと結果となります。 ***SQL [#m775cb3b] SELECT pvt.person ,pvt.Linux ,pvt.macOS ,pvt.Windows FROM ( SELECT person, product, quantity FROM sales ) T PIVOT ( SUM(quantity) FOR product IN (Linux,macOS,Windows) ) AS pvt ***結果 [#k1c7946b] |person|Linux|macOS|Windows|h |高橋|500|700|900| |佐藤|5|7|9| |鈴木|50|70|90| * 失敗例 [#cf2f6c00] 上記では、PIVOT対象となる表を以下のSQLで取得しています。~ ( SELECT person, product, quantity FROM sales ) T これは、salesテーブルにはsale_date(販売日)があり、以下のようにPIVOTすると、意図した動作になりません。 -SQL SELECT pvt.person ,pvt.Linux ,pvt.macOS ,pvt.Windows FROM sales PIVOT ( SUM(quantity) FOR product IN (Linux,macOS,Windows) ) AS pvt -結果 |person|Linux|macOS|Windows|h |高橋|100|200|300| |高橋|400|500|600| |佐藤|1|2|3| |佐藤|4|5|6| |鈴木|10|20|30| |鈴木|40|50|60| 販売日が複数あるので意図した動作になっていなのが確認できます。 以上、SQL ServerでPIVOTを使った記事でした。
