pivot句によるクロス集計 - HTN20190109の日記

MySQL

(8.0.31)

pivot句は使用できないため、case句で代用

Oracle

(19c)

https://style.potepan.com/articles/25072.html
https://oraclesqlpuzzle.ninja-web.net/oracle-sql-image/oracle-sql-image08.html

-- 1. テストテーブル作成

drop table tab1 purge;

create table tab1
(
salemonth varchar2(6),
productname varchar2(10),
amount int
);

-- 2. テストデータ作成

insert into tab1 values('202301','product01','10');
insert into tab1 values('202301','product01','20');
insert into tab1 values('202301','product01','30');
insert into tab1 values('202301','product02','40');
insert into tab1 values('202301','product02','50');
insert into tab1 values('202301','product02','60');
insert into tab1 values('202302','product01','70');
insert into tab1 values('202302','product01','80');
insert into tab1 values('202302','product01','90');
insert into tab1 values('202302','product02','100');
insert into tab1 values('202302','product02','110');
insert into tab1 values('202303','product01','120');
insert into tab1 values('202303','product01','130');
insert into tab1 values('202303','product02','140');
insert into tab1 values('202303','product02','150');

commit;

select * from tab1;

-- 3. pivot句によるクロス集計

select *
from tab1
pivot(
sum(amount)
for productname
in ('product01','product02')
)
order by salemonth
;

-- 4. pivot句の検証

select
salemonth salemonth
, sum(case productname when 'product01' then amount else 0 end) "'product01'"
, sum(case productname when 'product02' then amount else 0 end) "'product02'"
from tab1
group by salemonth
order by salemonth
;

-- 5. unpivot句による横縦変換

select *
from (
select *
from tab1
pivot(
sum(amount)
for productname
in ('product01','product02')
)
)
unpivot (
amount_total
for productname
in ("'product01'","'product02'")
)
order by salemonth,productname
;

-- 6. unpivot句の検証

select salemonth,productname,sum(amount) amount_total
from tab1
group by salemonth,productname
order by salemonth,productname
;

PostgreSQL

(15)

pivot句は使用できないため、case句で代用

SQL Server

(2019)

https://style.potepan.com/articles/25072.html
https://sqlserver.work/2020/07/27/case%e5%bc%8f%e3%82%92%e4%bd%bf%e7%94%a8%e3%81%97%e3%81%a6%e3%80%81%e3%82%af%e3%83%ad%e3%82%b9%e9%9b%86%e8%a8%88%e8%a1%a8%e3%82%92%e4%bd%9c%e6%88%90%e3%81%99%e3%82%8b/

-- 1. テストテーブル作成

drop table tab1;

create table tab1
(
salemonth varchar(6),
productname varchar(10),
amount bigint
);

-- 2. テストデータ作成

select * from tab1;

-- 3. pivot句によるクロス集計

select t1.salemonth, t1.product01, t1.product02
from tab1
pivot(
sum(amount)
for productname
in ([product01],[product02])
) t1
order by t1.salemonth
;

-- 4. pivot句の検証

select
salemonth salemonth
, sum(case productname when 'product01' then amount else 0 end) product01
, sum(case productname when 'product02' then amount else 0 end) product02
from tab1
group by salemonth
order by salemonth
;

-- 5. unpivot句による横縦変換

select t3.salemonth, t3.productname, t3.amount_total
from (
select t1.salemonth, t1.product01, t1.product02
from tab1
pivot(
sum(amount)
for productname
in ([product01],[product02])
) t1
) t2
unpivot (
amount_total
for productname
in ([product01],[product02])
) t3
order by t3.salemonth,t3.productname
;

-- 6. unpivot句の検証

select salemonth,productname,sum(amount) amount_total
from tab1
group by salemonth,productname
order by salemonth,productname
;