첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
유용한 소스 코드가 있으면 icodebroker@naver.com으로 보내주시면 감사합니다.
블로그 자료는 자유롭게 사용하세요.

■ 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

------------------------------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker
TAG

댓글을 달아 주세요