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

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

 

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

DECLARE @SourceSQL NVARCHAR(MAX);

SET @SourceSQL = '
SELECT
    [Name]                                      AS [Name]
   ,FileExtension                               AS FileExtension -- 예제를 위해 포함시킴
   ,CONVERT(NVARCHAR(4), YEAR(DateTimeWritten)) AS [Year]
   ,CONVERT(BIGINT, 1)                          AS CNT
FROM  PhotoBook WITH(NOLOCK)
WHERE MenuID          =  44
AND   CategoryName    =  ''자연''
AND   DateTimeWritten >= ''2010-01-01''
AND   DateTimeWritten <= ''2014-12-31''
';

EXECUTE PivotAggregate @SourceSQL -- 소스 SQL문       ; 타겟 필드 리스트, 피벗 필드, 값 필드에 명시된 컬럼이 나열되어야 한다.
    ,'Name,FileExtension'         -- 타겟 필드 리스트 ; 2개 이상인 경우 콤마로 구분한다.
    ,'Year'                       -- 피벗 필드
    ,'2010,2011,2012,2013,2014'   -- 피벗 필드
    ,'CNT'                        -- 값 필드
    ,'DESC'                       -- 정렬 순서        ; 'ASC' 또는 'DESC'
    ,10;                          -- TOP 카운트       ; 0인 경우 미적용

 

■ 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
----------------------------------------------------------------------------------------------------