이러한 테이블이 있습니다.
A B
-----------------
1 1
-----------------
1 2
-----------------
1 3
-----------------
1 4
-----------------
2 5
-----------------
2 6
-----------------
2 7
-----------------
2 8
-----------------
3 9
-----------------
3 10
-----------------
3 11
-----------------
3 12
-----------------
이 테이블을
A 값으로 묶어서 그룹을 만든 다음
그 그룹값들을 순차적으로 컬럼에 붙여 나가는 테이블로 바꾸고 싶습니다.
B1 B2 B3
---------------------------------------------------
1
---------------------------------------------------
2
---------------------------------------------------
3
---------------------------------------------------
4
---------------------------------------------------
1 5
---------------------------------------------------
2 6
---------------------------------------------------
3 7
---------------------------------------------------
4 8
---------------------------------------------------
1 5 9
---------------------------------------------------
2 6 10
--------------------------------------------------
3 7 11
---------------------------------------------------
4 8 12
---------------------------------------------------
또는 한 컬럼으로
Result
---------------------------------------------------
1
---------------------------------------------------
2
---------------------------------------------------
3
---------------------------------------------------
4
---------------------------------------------------
1,5
---------------------------------------------------
2,6
---------------------------------------------------
3,7
---------------------------------------------------
4,8
---------------------------------------------------
1,5,9
---------------------------------------------------
2,6,10
--------------------------------------------------
3,7,11
---------------------------------------------------
4,8,12
---------------------------------------------------
고민해봐도 감도 못잡아서 이렇게 질문해봅니다//
감사합니다~
Comment 2
-
withSQLServer
2018.04.20 12:42
-
건우아빠
2018.04.20 22:06
방법은 여러가지가 있을겁니다.DECLARE @RES TABLE(A int ,B int) ;insert @RESselect 1 A, 1 B union allselect 1 A, 2 B union allselect 1 A, 3 B union allselect 1 A, 4 B union allselect 2 A, 5 B union allselect 2 A, 6 B union allselect 2 A, 7 B union allselect 2 A, 8 B union allselect 3 A, 9 B union allselect 3 A, 10 B union allselect 3 A, 11 B union allselect 3 A, 12 B union allselect 4 A, 13 B union allselect 4 A, 14 B union allselect 4 A, 15 B union allselect 4 A, 16 B union allselect 5 A, 17 B union allselect 5 A, 18 B union allselect 5 A, 19 B union allselect 5 A, 20 B--------------------------------------------------------------;withres as (select *from ( select * , ROW_NUMBER() over (partition by A order by B) rno from @RES ) sPIVOT(max(b)for a in ([1],[2],[3],[4],[5])) AS Pvt)select *from ( select distinct a from @RES ) raouter apply(select case when ra.A >= 1 then [1] end [b1], case when ra.A >= 2 then [2] end [b2], case when ra.A >= 3 then [3] end [b3], case when ra.A >= 4 then [4] end [b4], case when ra.A >= 5 then [5] end [b5]from res) rb
