[PostqreSQL] PIVOT & UNPIVOT

PIVOT 샘플 테이블 

SELECT 1 AS CUSTOMER_ID, 100 AS Q1, 210 AS Q2, 203 AS Q3, 304 AS Q4 
UNION ALL
SELECT 2 AS CUSTOMER_ID, 150 AS Q1, 118 AS Q2, 423 AS Q3, 257 AS Q4 
UNION ALL
SELECT 3 AS CUSTOMER_ID, 220 AS Q1, 311 AS Q2, 351 AS Q3, 269 AS Q4 
UNION ALL
SELECT 4 AS CUSTOMER_ID, 330 AS Q1, 406 AS Q2, 652 AS Q3, 956 AS Q4

PIVOT

WITH RECURSIVE TEMP_PIVOT (CUSTOMER_ID, Q1, Q2, Q3, Q4)
AS
(
	SELECT 1 AS CUSTOMER_ID, 100 AS Q1, 210 AS Q2, 203 AS Q3, 304 AS Q4 UNION ALL
	SELECT 2 AS CUSTOMER_ID, 150 AS Q1, 118 AS Q2, 423 AS Q3, 257 AS Q4 UNION ALL
	SELECT 3 AS CUSTOMER_ID, 220 AS Q1, 311 AS Q2, 351 AS Q3, 269 AS Q4 UNION ALL
	SELECT 4 AS CUSTOMER_ID, 330 AS Q1, 406 AS Q2, 652 AS Q3, 956 AS Q4 
)
SELECT C.CUSTOMER_ID, T.*
  FROM TEMP_PIVOT C
 CROSS JOIN LATERAL (VALUES (C.Q1, 'Q1')
						   ,(C.Q2, 'Q2')
						   ,(C.Q3, 'Q3')
						   ,(C.Q4, 'Q4')
					) AS T(TURNOVER, QUARTER)
ORDER BY CUSTOMER_ID, QUARTER

PIVOT 결과

UNPIVOT 샘픔 테이블

SELECT 1 AS CUSTOMER_ID, 101 AS TURNOVER, 'Q1' AS QUARTER 
UNION ALL
SELECT 1 AS CUSTOMER_ID, 102 AS TURNOVER, 'Q2' AS QUARTER 
UNION ALL
SELECT 1 AS CUSTOMER_ID, 103 AS TURNOVER, 'Q3' AS QUARTER 
UNION ALL
SELECT 1 AS CUSTOMER_ID, 104 AS TURNOVER, 'Q4' AS QUARTER 
UNION ALL
SELECT 2 AS CUSTOMER_ID, 201 AS TURNOVER, 'Q1' AS QUARTER 
UNION ALL
SELECT 2 AS CUSTOMER_ID, 202 AS TURNOVER, 'Q2' AS QUARTER 
UNION ALL
SELECT 2 AS CUSTOMER_ID, 203 AS TURNOVER, 'Q3' AS QUARTER 
UNION ALL
SELECT 2 AS CUSTOMER_ID, 204 AS TURNOVER, 'Q4' AS QUARTER 
UNION ALL
SELECT 3 AS CUSTOMER_ID, 301 AS TURNOVER, 'Q1' AS QUARTER 
UNION ALL
SELECT 3 AS CUSTOMER_ID, 302 AS TURNOVER, 'Q2' AS QUARTER 
UNION ALL
SELECT 3 AS CUSTOMER_ID, 303 AS TURNOVER, 'Q3' AS QUARTER 
UNION ALL
SELECT 3 AS CUSTOMER_ID, 304 AS TURNOVER, 'Q4' AS QUARTER 
UNION ALL
SELECT 4 AS CUSTOMER_ID, 401 AS TURNOVER, 'Q1' AS QUARTER 
UNION ALL
SELECT 4 AS CUSTOMER_ID, 402 AS TURNOVER, 'Q2' AS QUARTER 
UNION ALL
SELECT 4 AS CUSTOMER_ID, 403 AS TURNOVER, 'Q3' AS QUARTER 
UNION ALL
SELECT 4 AS CUSTOMER_ID, 404 AS TURNOVER, 'Q4' AS QUARTER

UNPIVOT 샘플

UNPIVOT

WITH RECURSIVE TEMP_UNPIVOT (CUSTOMER_ID, TURNOVER, QUARTER)
AS
(
	WITH RECURSIVE TEMP_PIVOT (CUSTOMER_ID, Q1, Q2, Q3, Q4)
	AS
	(
		SELECT 1 AS CUSTOMER_ID, 100 AS Q1, 210 AS Q2, 203 AS Q3, 304 AS Q4 UNION ALL
		SELECT 2 AS CUSTOMER_ID, 150 AS Q1, 118 AS Q2, 423 AS Q3, 257 AS Q4 UNION ALL
		SELECT 3 AS CUSTOMER_ID, 220 AS Q1, 311 AS Q2, 351 AS Q3, 269 AS Q4 UNION ALL
		SELECT 4 AS CUSTOMER_ID, 330 AS Q1, 406 AS Q2, 652 AS Q3, 956 AS Q4 
	)
	SELECT C.CUSTOMER_ID, T.*
	  FROM TEMP_PIVOT C
	 CROSS JOIN LATERAL (VALUES (C.Q1, 'Q1')
							   ,(C.Q2, 'Q2')
							   ,(C.Q3, 'Q3')
							   ,(C.Q4, 'Q4')
						) AS T(TURNOVER, QUARTER)
	ORDER BY CUSTOMER_ID, QUARTER
)
	SELECT C.CUSTOMER_ID
	      ,MAX(CASE WHEN C.QUARTER = 'Q1' THEN C.TURNOVER END) AS Q1
	      ,MAX(CASE WHEN C.QUARTER = 'Q2' THEN C.TURNOVER END) AS Q2
	      ,MAX(CASE WHEN C.QUARTER = 'Q3' THEN C.TURNOVER END) AS Q3
	      ,MAX(CASE WHEN C.QUARTER = 'Q4' THEN C.TURNOVER END) AS Q4
	  FROM TEMP_UNPIVOT C
     GROUP BY C.CUSTOMER_ID

 

UNPIVOT 결과