요일 시작시간 종료시간 사람
11 9 10 a1
11 9 10 a2
11 10 11 b1
11 10 11 b2
11 11 12 c1
11 11 12 c2
위의 테이블을
요일 시작시간(1) 종료시간(1) 사람(1) 시작시간(2) 종료시간(2) 사람(2) 시작시간(3) 종료시간(3) 사람(3)
11 9 10 a1 10 11 b1 11 12 c1
11 9 10 a2 10 11 b2 11 12 c2
아래의 테이블처럼 나타내고 싶은데 방법이 없을까요?
Comment 1
-
지영아빠
2021.06.29 09:02
참고하세요..
with t1 as (
select 11 as w, 9 as s_time, 10 as e_time , 'a1'as p union all
select 11, 9, 10, 'a2' union all
select 11, 10, 11, 'b1' union all
select 11, 10, 11, 'b2' union all
select 11, 11, 12, 'c1' union all
select 11, 11, 12, 'c2'
)
select
w,
max(case when cid = 1 then s_time else null end) as s_time_1,
max(case when cid = 1 then e_time else null end) as e_time_1,
max(case when cid = 1 then p else null end) as p_1,
max(case when cid = 2 then s_time else null end) as s_time_2,
max(case when cid = 2 then e_time else null end) as e_time_2,
max(case when cid = 2 then p else null end) as p_2,
max(case when cid = 3 then s_time else null end) as s_time_3,
max(case when cid = 3 then e_time else null end) as e_time_3,
max(case when cid = 3 then p else null end) as p_3
from (
select
* ,
right(p,1) as rid ,
rank() over (PARTITION by right(p,1) order by s_time) as cid
from
t1
) x
group by
rid, w