mssql unpivot 관련 문의드립니다. 0 4 2,954

by 리리리1 [SQL Query] [2024.03.22 15:03:11]



UNPIVOT으론 한계가 있는거 같은데 컬럼을 세개씩 끊은후에 (abc , def , ghi) 아래와같이 표현이 가능할까요???

 

 

by 뉴비디비 [2024.03.22 21:59:25]
-- UNPIVOT 으로 될꺼예요. 

WITH tbl (idx, a,b,c,d,e,f,g,h,i) AS (
	SELECT 1, '1000','2000','3000','4000','5000','6000','7000','8000','9000'
	UNION ALL  
	SELECT 2, '1100','2200','3300','4400','5500','6600','7700','8800','9900'
)
SELECT 
	idx 
	, min( CASE WHEN sortidx = 1 THEN kiki END ) AS z1
	, min( CASE WHEN sortidx = 2 THEN kiki END ) AS z2
	, min( CASE WHEN sortidx = 3 THEN kiki END ) AS z3
FROM (
	SELECT 
		idx, kiki, cols
		, (RANK() OVER(PARTITION BY idx ORDER BY cols)+2)/3 AS sortidx
		, (ROW_NUMBER() OVER(PARTITION BY idx ORDER BY cols)-1)%3 AS grpidx
	FROM tbl
		UNPIVOT ( kiki FOR cols IN (a,b,c,d,e,f,g,h,i) ) AS unpvt
) tmp
GROUP BY idx, grpidx
ORDER BY idx, grpidx

 


by 리리리1 [2024.03.27 08:26:36]

감사합니다 참고해보겠습니다~


by 마농 [2024.03.24 23:59:32]

Oracle 에서는 다중 항목으로 피벗/언피벗 이 가능한데
MSSQL 에서는 그게 안되네요.
좀 더 복잡하게 구현해야 하겠네요.
 

WITH tbl(idx, a, b, c, d, e, f, g, h, i) AS
(
SELECT 1, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000 FROM dual
 UNION ALL 
SELECT 2, 1100, 2200, 3300, 4400, 5500, 6600, 7700, 8800, 9900 FROM dual
)
-- Oracle --
SELECT *
  FROM tbl
 UNPIVOT ( (z1, z2, z3) FOR gb IN ( (a, d, g) AS 1
                                  , (b, e, h) AS 2
                                  , (c, f, i) AS 3
                                  ) )
;
WITH tbl(idx, a, b, c, d, e, f, g, h, i) AS
(
SELECT 1, 1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000
 UNION ALL 
SELECT 2, 1100, 2200, 3300, 4400, 5500, 6600, 7700, 8800, 9900
)
-- MSSQL --
SELECT *
  FROM (SELECT idx, z
             , CONCAT('z', (ROW_NUMBER() OVER(PARTITION BY idx ORDER BY gb) + 2) / 3) x
             , (ROW_NUMBER() OVER(PARTITION BY idx ORDER BY gb) - 1) % 3 + 1 gb
          FROM tbl
         UNPIVOT (z FOR gb IN (a, b, c, d, e, f, g, h, i)) a
        ) a
 PIVOT (MIN(z) FOR x IN ([z1], [z2], [z3])) a
 ORDER BY idx, gb
;

 


by 리리리1 [2024.03.27 08:26:43]

감사합니다 참고해보겠습니다~

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입