첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
본 블로그는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 블로그 콘텐츠 향상을 위해 쓰여집니다.

728x90
반응형
728x170

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

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 함수를 이용한 동적 집계하기

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
728x90
반응형
그리드형
Posted by 사용자 icodebroker

댓글을 달아 주세요