개발/MS-SQL
MSSQL로 열병합 하기!
쭈니후니
2012. 2. 10. 14:22
아래쿼리를 잘 활용해서 테이블 ROWSPAN 효과를 볼수있네요^^
with tt( c1, c2, c3, detail )
as (
select '1', '1', '1', 'a'
union all select '1', '1', '2', 'b'
union all select '1', '2', '3', 'c'
union all select '1', '2', '4', 'd'
union all select '2', '3', '5', 'e'
union all select '2', '3', '6', 'f'
union all select '2', '4', '7', 'g'
union all select '2', '4', '8', 'h'
)
select case when uu.c1 = vv.c1 then '' else uu.c1 end as c1
, case when uu.c2 = vv.c2 then '' else uu.c2 end as c2
, case when uu.c3 = vv.c3 then '' else uu.c3 end as c3
, uu.detail
from
(
select ROW_NUMBER() over ( order by c1, c2, c3 ) as rid,
c1, c2, c3, detail
from tt
) as uu
left join
(
select ROW_NUMBER() over ( order by c1, c2, c3 ) as rid,
c1, c2, c3, detail
from tt
) as vv on uu.rid = vv.rid + 1
;
결과전
1 1 1 a
1 1 2 b
1 2 3 c
1 2 4 d
2 3 5 e
2 3 6 f
2 4 7 g
2 4 8 h
결과후
1 1 1 a
2 b
2 3 c
4 d
2 3 5 e
6 f
4 7 g
8 h
결과전
1 1 1 a
1 1 2 b
1 2 3 c
1 2 4 d
2 3 5 e
2 3 6 f
2 4 7 g
2 4 8 h
결과후
1 1 1 a
2 b
2 3 c
4 d
2 3 5 e
6 f
4 7 g
8 h