c1 | c2 | c3
10 | 11 | 9
1 | 2 | 3
3 | 1 | 2
이런 형태의 테이블 구조에서 select 결과를
r1 | r2 | r3
9 | 10 | 11
이렇게 정렬 처리로 가능할까요? case 구문으로 도배를 해야 하는지;;;;;
고맙습니다.
case 문 도배가 성능상 제일 유리할 것으로 보입니다.
더 좋은 방법도 많겠지만
CASE문 아닌 ROW_NUMBER()를 사용해서 한번 해봤습니다.
; WITH TBL1 AS ( SELECT 10 AS C1, 11 AS C2, 9 AS C3 UNION ALL SELECT 10 AS C1, 11 AS C2, 9 AS C3 UNION ALL SELECT 1 AS C1, 2 AS C2, 3 AS C3 UNION ALL SELECT 3 AS C1, 1 AS C2, 2 AS C3 ) , TBL2 AS ( SELECT ROW_NUMBER() OVER (ORDER BY C1) AS ROW_NUM , C1, C2, C3 FROM TBL1 )
SELECT MAX(CASE WHEN ROW_NUM2 = 1 THEN C1 ELSE NULL END) AS C1 , MAX(CASE WHEN ROW_NUM2 = 2 THEN C1 ELSE NULL END) AS C2 , MAX(CASE WHEN ROW_NUM2 = 3 THEN C1 ELSE NULL END) AS C3 FROM ( SELECT ROW_NUM , C1 , ROW_NUMBER() OVER (PARTITION BY ROW_NUM ORDER BY C1) AS ROW_NUM2 FROM ( SELECT ROW_NUM, C1 FROM TBL2 UNION ALL SELECT ROW_NUM, C2 FROM TBL2 UNION ALL SELECT ROW_NUM, C3 FROM TBL2 ) AS B ) AS A GROUP BY ROW_NUM