MSSQL로 열병합 하기!

아래쿼리를 잘 활용해서 테이블 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