MS-SQL에서 PIVOT을 이용하여 조회할 때 가변칼럼 형태로 조회결과 가져오기, 동적피벗 사용하기

MSSQL에서 피벗 테이블을 이용해 가변 칼럼을 조회하려면 동적 피벗(Dynamic Pivot) 방식을 사용해야 합니다. 고정된 값이 아닌 동적으로 변하는 컬럼 값을 처리하기 위해서 SQL 쿼리를 동적으로 생성하여 실행합니다. 이는 일반적인 PIVOT 구문으로는 처리하기 어려운 상황을 해결하기 위한 방법입니다.

다음은 동적 피벗을 사용하는 기본적인 흐름입니다.

1. 동적으로 변하는 컬럼 값을 가져오기

먼저, 피벗할 컬럼 값들을 조회하여 변수에 저장합니다.

DECLARE @columns NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(컬럼명), ',')
FROM (SELECT DISTINCT 컬럼명 FROM 테이블명) AS 컬럼들;

• STRING_AGG는 여러 값을 구분자로 묶어 문자열로 변환하는 함수입니다. (SQL Server 2017 이상에서 사용 가능)
• QUOTENAME는 컬럼명을 대괄호([])로 감싸 SQL 인젝션을 방지합니다.

2. 동적 SQL 쿼리 작성

다음으로, 동적으로 피벗 쿼리를 생성하여 실행합니다.

DECLARE @sql NVARCHAR(MAX);

SET @sql = '
SELECT *
FROM (
    SELECT 행을 나눌 컬럼, 피벗할 값, 가변컬럼명
    FROM 테이블명
) AS SourceTable
PIVOT (
    MAX(피벗할 값)
    FOR 가변컬럼명 IN (' + @columns + ')
) AS PivotTable;';

EXEC sp_executesql @sql;

• @columns에는 첫 번째 단계에서 동적으로 생성한 컬럼 목록이 들어갑니다.
• sp_executesql을 사용해 동적 쿼리를 실행합니다.

728x90

예시

아래는 직원의 연도별 판매량을 가변 칼럼으로 피벗하는 예시입니다.

테이블 구조 (SalesTable)

Employee ID	Year	Sales
1	2021	100
1	2022	150
2	2021	200
2	2022	250

동적 피벗 쿼리

DECLARE @columns NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(Year), ',')
FROM (SELECT DISTINCT Year FROM SalesTable) AS Years;

DECLARE @sql NVARCHAR(MAX);

SET @sql = '
SELECT EmployeeID, ' + @columns + '
FROM (
    SELECT EmployeeID, Year, Sales
    FROM SalesTable
) AS SourceTable
PIVOT (
    MAX(Sales)
    FOR Year IN (' + @columns + ')
) AS PivotTable;';

EXEC sp_executesql @sql;

결과

EmployeeID	2021	2022
1	100	150
2	200	250

이 방식으로 가변적인 피벗 컬럼을 동적으로 처리할 수 있습니다.