Pivot table

CREATE TABLE pivot (
    id INT auto_increment NOT NULL,
    name varchar(100) NULL,
    group_name varchar(100) NULL,
    CONSTRAINT PIVOT_PK PRIMARY KEY (id)
)

위와 같은 테이블에

 

 insert into pivot (name, group_name)
 values ('a-1', 'A')
 , ('b-1', 'B')
 , ('b-2', 'B')
 , ('c-1', 'C')
 , ('d-1', 'D')
 , ('d-2', 'D')
 , ('d-3', 'D')
 , ('b-3', 'B')
 , ('a-2', 'A')
 , ('c-2', 'C')
 
  
 select *
 from pivot

id	name	group_name
1	a-1	A
2	b-1	B
3	b-2	B
4	c-1	C
5	d-1	D
6	d-2	D
7	d-3	D
8	b-3	B
9	a-2	A
10	c-2	C

위와 같은 데이터가 있다고 가정할때

 

A	B	C	D
a-1	b-1	c-1	d-1
a-2	b-2	c-2	d-2
NULL	b-3	NULL	d-3

결과적으로 위와같은 결과 값을 도출하고자 한다.

 

 

select p.*
, (case @gname when p.group_name then @rnum:=@rnum+1 else @rnum:=1 end) as rnum
, (@gname:=p.group_name) as gname
from pivot p, (select @gname:= '', @rnum:=0) as b
order by p.group_name, name

id	name	group_name	rnum	gname
1	a-1	A		1.0	A
9	a-2	A		2.0	A
2	b-1	B		1.0	B
3	b-2	B		2.0	B
8	b-3	B		3.0	B
4	c-1	C		1.0	C
10	c-2	C		2.0	C
5	d-1	D		1.0	D
6	d-2	D		2.0	D
7	d-3	D		3.0	D

1. group_name을 기준으로 오름차순정렬

2. 로우를 순서대로 읽으면서 group_name을 @gname과 비교하여 @rnum의 값을 1부터 순차적으로 올린다.

 

 select 
 	group_concat(if(p.gname = 'A', name, null)) as A
 	, group_concat(if(p.gname = 'B', name, null)) as B
 	, group_concat(if(p.gname = 'C', name, null)) as C
 	, group_concat(if(p.gname = 'D', name, null)) as D
 from (
	 select p.*
	 	, (case @gname when p.group_name then @rnum:=@rnum+1 else @rnum:=1 end) as rnum
	 	, (@gname:=p.group_name) as gname
	 from pivot p, (select @gname:= '', @rnum:=0) as b
	 order by p.group_name, name
 ) p
 group by p.rnum


A	B	C	D
a-1	b-1	c-1	d-1
a-2	b-2	c-2	d-2
NULL	b-3	NULL	d-3

이전 결과 쿼리를 from절의 서브쿼리로 묶고 @rnum을 기준으로 group by 하여

순차적으로 출력한다.

 

 

 

 

 

 

 

https://mia-dahae.tistory.com/82

[MySQL] 처음 사용해보는 PIVOT

https://modern-sql.com/use-case/pivot

SQL Pivot in all databases: MySQL, MariaDB, SQLite, PostgreSQL, Oracle, …

https://www.hackerrank.com/challenges/occupations/problem

Occupations | HackerRank