[MSSQL] 동적 PIVOT, 동적 UNPIVOT (dynamic pivot/unpivot)

 

 

 

[MSSQL] PIVOT, UNPIVOT (행렬 변환)

 

 

PIVOT / UNPIVOT

PIVOT 과 UNPIVOT

 

-- PIVOT

SELECT 컬럼명 FROM
테이블명
PIVOT([집계 함수]([집계 컬럼명]) FOR [대상 컬럼명] IN ([컬럼이 될 값])) AS 피벗테이블명

 

-- UNPIVOT

SELECT 컬럼명
FROM 테이블명
UNPIVOT([집계될 데이터의 컬럼명] FOR [UNPIVOT할 컬럼 데이터의 컬럼명] IN ([UNPIVOT할 컬럼명])) AS 언피벗테이블명

 

일반적으로 PIVOT과 UNPIVOT의 대상 컬럼과 컬럼이 될 값을 지정해줘야 한다.

그런데 만약 컬럼명이나 행의 값들이 동적이라면?

이를 고려하여 동적 PIVOT / UNPIVOT 쿼리를 작성해보자

 

 

 

동적 PIVOT

DECLARE @Query VARCHAR(MAX);

SET @Query = '';
SELECT @Query = @Query + '[' +  반 + '],'
FROM (SELECT DISTINCT 반 FROM TABLE_A) A

SET @Query = LEFT(@Query, LEN(@Query)-1) -- [1반],[2반]

-- 실행
EXEC ('SELECT * FROM TABLE_A PIVOT(SUM(점수) FOR 반 IN (' + @Query + ')) AS PVT')

 

원본 데이터

결과 데이터

 

 

 

동적 UNPIVOT

DECLARE @Query VARCHAR(MAX);
DECLARE @strColumn VARCHAR(MAX);
DECLARE @keyColumn VARCHAR(100) = '학년';

-- 컬럼명 추출
SELECT name INTO #TEMP
FROM sys.all_columns
WHERE object_id = object_id('TABLE_A')
AND name <> @keyCloumn

-- 컬럼명 한줄로 처리
SET @strColumn = STUFF((SELECT '],[' + name FROM #TEMP FOR XML PATH('')) + ']', 1, 1, '');
SET @strColumn = RIGHT(@strColumn, LEN(@strColumn)-1); -- [1반],[2반]

-- UNPIVOT 쿼리 작성
SET @Query = 'SELECT * FROM TABLE_A UNPIVOT([점수] FOR 반 IN (' + @strColumn + ')) AS UNPVT'

-- 실행
EXEC (@Query)

 

원본 데이터

결과 데이터