[DATABASE/SQLSERVER] PIVOT 함수를 이용한 동적 집계하기

■ PIVOT 함수를 이용해 동적 집계를 하는 방법을 보여준다.

▶ PIVOT 함수를 이용한 동적 집계하기 예제 (SQL)

■ PIVOT 함수를 이용한 동적 집계하기 (SQL)
—————————————————————————————————-
CREATE PROCEDURE [dbo].[PivotAggregate]
@P_SourceSQL NVARCHAR(MAX) — 소스 SQL문
,@P_TargetFieldList NVARCHAR(MAX) — 타겟 필드 리스트
,@P_PivotField NVARCHAR(100) — 피벗 필드
,@P_PivotValueList NVARCHAR(MAX) — 피벗 값 리스트
,@P_ValueField NVARCHAR(100) — 값 필드
,@P_SortOrder NVARCHAR(4) — 정렬 순서
,@P_TopCount INT — TOP 카운트
AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

— 임시 피벗 테이블을 생성한다.
CREATE TABLE #Pivot
(
PivotField NVARCHAR(100) — 피벗 필드
);

— 임시 피벗 테이블에 데이터를 추가한다.
INSERT INTO #Pivot
SELECT A.Item AS PivotField
FROM dbo.GetTable(@P_PivotValueList, ',') AS A
ORDER BY A.Item ASC;

— 집계 SQL문 생성시 필요한 리스트를 정의한다.
DECLARE @PivotList NVARCHAR(MAX); — Pivot 리스트
DECLARE @TotalSumList NVARCHAR(MAX); — 총계 리스트
DECLARE @FinalSELECTList NVARCHAR(MAX); — 최종 SELECT 리스트

SET @PivotList = '';
SET @TotalSumList = '';
SET @FinalSELECTList = '';

— 커서 1을 생성한다.
DECLARE @PivotField NVARCHAR(100);

DECLARE Cursor1 CURSOR FOR
SELECT PivotField
FROM #Pivot
ORDER BY PivotField ASC;

— 커서 1을 오픈한다.
OPEN Cursor1;

— 커서1의 레코드를 순회한다.
FETCH NEXT FROM Cursor1 INTO @PivotField;

WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@PivotList) > 0
BEGIN
SET @PivotList = @PivotList + ','; — Pivot 리스트
SET @TotalSumList = @TotalSumList + '+'; — 총계 리스트
SET @FinalSELECTList = @FinalSELECTList + ','; — 최종 SELECT 리스트
END;

SET @PivotList = @PivotList + '[' + @PivotField + ']'; — Pivot 리스트
SET @TotalSumList = @TotalSumList + 'ISNULL([' + @PivotField + '], 0)'; — 총계 리스트
SET @FinalSELECTList = @FinalSELECTList + 'ISNULL([' + @PivotField + '], 0) AS [' + @PivotField + ']'; — 최종 SELECT 리스트

FETCH NEXT FROM Cursor1 INTO @PivotField;
END;

— 커서 1을 닫는다.
CLOSE Cursor1;

DEALLOCATE Cursor1;

— 집계한다.
IF @P_TopCount > 0
BEGIN
SET ROWCOUNT @P_TopCount;
END;

SET @SQL = N'
SELECT
' + @P_TargetFieldList + '
,' + @FinalSELECTList + '
,TotalSum
FROM
(
SELECT
*
,(' + @TotalSumList + ') AS TotalSum
FROM
(
SELECT
' + @P_TargetFieldList + '
,' + @P_PivotField + '
,' + @P_ValueField + '
FROM
(
' + @P_SourceSQL + '
) AS A
) AS A
PIVOT(SUM([' + @P_ValueField + ']) FOR [' + @P_PivotField + '] IN (' + @PivotList + ')) AS B
) AS A ORDER BY TotalSum ' + @P_SortOrder;

EXECUTE SP_EXECUTESQL @SQL;

SET ROWCOUNT 0;

DROP TABLE #Pivot;
END
—————————————————————————————————-