1. 개요

这篇文章探讨了SQL Server에서 행을 열로 변환하는 다양한 기법을 소개합니다. 특히 동적 PIVOT 연산자를 사용하여 다양한 요구사항에 대응하는 방법을 상세히 설명합니다. 정적 방법과 동적 방법을 모두 다루며, 특히 테이블명, 그룹화 열, 변환 필드, 값 필드를 매개변수로 처리할 수 있는 유연한 구조를 제공합니다.

2. 배경

행到열 변환은 데이터베이스에서 자주 사용되는 대표적인 데이터 변환 기법 중 하나입니다. 그러나 대부분의 기존 예제들은 단순한 정적 구현에 불과하며, 실제 업무 환경에서는 테이블 구조나 필드명이 상황에 따라 달라질 수 있습니다. 따라서 본 문서에서는 테이블명, 그룹화 열, 행到열 필드, 값 필드를 실제 매개변수로 전달받을 수 있는 동적 구현체를 제공합니다. 이를 통해 개발자는 최소한의 수정만으로 다양한 데이터셋에 적용할 수 있습니다.

3. 구현 코드

(1) 먼저 테스트용 테이블을 생성하고 샘플 데이터를 삽입합니다:

--테스트 테이블 생성
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExamScores]') AND type IN (N'U'))
    DROP TABLE [dbo].[ExamScores]
GO

CREATE TABLE [dbo].[ExamScores](
    [ScoreID] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [nvarchar](50) NULL,
    [Subject] [nvarchar](50) NULL,
    [Points] [decimal](18, 2) NULL
)
GO

--샘플 데이터 삽입
INSERT INTO [ExamScores] ([StudentName], [Subject], [Points])
SELECT N'김철수', N'국어', 75.5 UNION ALL
SELECT N'박영희', N'수학', 82.0 UNION ALL
SELECT N'최민수', N'영어', 88.5 UNION ALL
SELECT N'최민수', N'수학', 91.0 UNION ALL
SELECT N'박영희', N'국어', 79.0 UNION ALL
SELECT N'김철수', N'영어', 85.5 UNION ALL
SELECT N'최민수', N'국어', 76.0

SELECT * FROM [ExamScores]

(2) 정적 CASE 문을 사용한 행到열 변환:

--정적 열 변환 (고정 필드)
SELECT [StudentName],
    SUM(CASE [Subject] WHEN '수학' THEN [Points] ELSE 0 END) AS '[수학]',
    SUM(CASE [Subject] WHEN '영어' THEN [Points] ELSE 0 END) AS '[영어]',
    SUM(CASE [Subject] WHEN '국어' THEN [Points] ELSE 0 END) AS '[국어]'
FROM [ExamScores]
GROUP BY [StudentName]
GO

(3) 동적 SQL拼接을 사용한 행到열 변환 (SQL Server 2000 이상 호환):

--동적 SQL拼接을 통한 행到열
DECLARE @query VARCHAR(MAX)
SET @query = 'SELECT [StudentName],'

SELECT @query = @query + 
    'SUM(CASE [Subject] WHEN ''' + [Subject] + ''' THEN [Points] ELSE 0 END) AS ' + QUOTENAME([Subject]) + ','
FROM (SELECT DISTINCT [Subject] FROM [ExamScores]) AS SubjectList

SET @query = LEFT(@query, LEN(@query) - 1) + ' FROM [ExamScores] GROUP BY [StudentName]'

PRINT(@query)
EXEC(@query)
GO

(4) SQL Server 2005 이상의 PIVOT 연산자를 사용한 정적 구현:

--PIVOT을 사용한 정적 행到열
SELECT *
FROM (
    SELECT [StudentName], [Subject], [Points]
    FROM [ExamScores]
) SourceData
PIVOT (
    SUM([Points]) FOR [Subject] IN ([수학], [영어], [국어])
) AS PivotResult
ORDER BY PivotResult.[StudentName]
GO

(5) 동적 PIVOT 구현 - 열 필드가 자동으로 생성됩니다:

--동적 PIVOT 구현
DECLARE @queryString VARCHAR(MAX)
DECLARE @columnList VARCHAR(MAX)

SELECT @columnList = ISNULL(@columnList + ',', '') + QUOTENAME([Subject])
FROM [ExamScores]
GROUP BY [Subject]

SET @queryString = 'SELECT * FROM (
    SELECT [StudentName], [Subject], [Points] 
    FROM [ExamScores]
) SourceData 
PIVOT (
    SUM([Points]) FOR [Subject] IN (' + @columnList + ')
) AS PivotResult 
ORDER BY PivotResult.[StudentName]'

PRINT(@queryString)
EXEC(@queryString)
GO

(6) 완전한 매개변수화된 동적 PIVOT - 다양한 테이블과 필드에 재사용 가능:

--매개변수화된 동적 PIVOT 프로시저
DECLARE @finalQuery NVARCHAR(MAX)
DECLARE @dynamicColumns NVARCHAR(MAX)

DECLARE @sourceTable NVARCHAR(128)  --대상 테이블명
DECLARE @groupByColumn NVARCHAR(128) --그룹화할 열
DECLARE @pivotColumn NVARCHAR(128)   --행→열로 변환할 열
DECLARE @valueColumn NVARCHAR(128)   --집계할 값 열

SET @sourceTable = 'ExamScores'
SET @groupByColumn = 'StudentName'
SET @pivotColumn = 'Subject'
SET @valueColumn = 'Points'

--동적 열 목록 생성
SET @finalQuery = N'SELECT @columnsOutput = ISNULL(@columnsOutput + '','','''') + QUOTENAME([' + @pivotColumn + ']) 
FROM [' + @sourceTable + '] GROUP BY [' + @pivotColumn + ']'

EXEC sp_executesql @finalQuery, 
    N'@columnsOutput NVARCHAR(MAX) OUTPUT', 
    @columnsOutput = @dynamicColumns OUTPUT

--최종 PIVOT 쿼리 실행
SET @finalQuery = N'SELECT * FROM (
    SELECT [' + @groupByColumn + '], [' + @pivotColumn + '], [' + @valueColumn + '] 
    FROM [' + @sourceTable + ']
) SourceData 
PIVOT (
    SUM([' + @valueColumn + ']) FOR [' + @pivotColumn + '] IN (' + @dynamicColumns + ')
) AS PivotResult 
ORDER BY PivotResult.[' + @groupByColumn + ']'

EXEC(@finalQuery)

(7) 조건 필터링이 포함된 매개변수화된 동적 PIVOT:

--필터 조건이 있는 매개변수화된 동적 PIVOT
DECLARE @queryBuilder NVARCHAR(MAX)
DECLARE @columnNames NVARCHAR(MAX)
DECLARE @filterCondition NVARCHAR(MAX)

DECLARE @targetTable NVARCHAR(128)
DECLARE @groupColumn NVARCHAR(128)
DECLARE @pivotField NVARCHAR(128)
DECLARE @aggField NVARCHAR(128)

SET @targetTable = 'ExamScores'
SET @groupColumn = 'StudentName'
SET @pivotField = 'Subject'
SET @aggField = 'Points'
SET @filterCondition = 'WHERE StudentName = ''최민수'''

--필터 조건을 포함한 동적 열 생성
SET @queryBuilder = N'SELECT @colOutput = ISNULL(@colOutput + '','','''') + QUOTENAME([' + @pivotField + ']) 
FROM [' + @targetTable + '] ' + @filterCondition + ' GROUP BY [' + @pivotField + ']'

EXEC sp_executesql @queryBuilder, 
    N'@colOutput NVARCHAR(MAX) OUTPUT', 
    @colOutput = @columnNames OUTPUT

--필터 조건이 적용된 최종 쿼리
SET @queryBuilder = N'SELECT * FROM (
    SELECT [' + @groupColumn + '], [' + @pivotField + '], [' + @aggField + '] 
    FROM [' + @targetTable + '] ' + @filterCondition + ') SourceData 
PIVOT (
    SUM([' + @aggField + ']) FOR [' + @pivotField + '] IN (' + @columnNames + ')
) AS PivotResult 
ORDER BY PivotResult.[' + @groupColumn + ']'

EXEC(@queryBuilder)

4. 참고 자료

Microsoft 공식 문서: PIVOT 및 UNPIVOT 연산자 사용