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

■ GROUP BY-CASE문을 이용한 동적 집계하기 예제

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

DECLARE @SourceSQL NVARCHAR(MAX);

 

SET @SourceSQL = N'

SELECT

    [Name]                                      AS [Name]

   ,FileExtension                               AS FileExtension

   ,CONVERT(NVARCHAR(4), YEAR(DateTimeWritten)) AS [Year]

   ,CONVERT(INT, 1)                             AS CNT

FROM  PhotoBook WITH(NOLOCK)

WHERE MenuID          =  44

AND   CategoryName    =  ''자연''

AND   DateTimeWritten >= ''2006-01-01''

AND   DateTimeWritten <  ''2011-01-01''

';

 

EXECUTE SQLAggregate @SourceSQL      -- 소스 SQL문       ; 타겟 필드 리스트, 피벗 필드, 값 필드에 명시된 컬럼이 나열되어야 한다.

    ,'Name,FileExtension'           -- 타겟 필드 리스트 ; 2개 이상인 경우 콤마로 구분한다.

    ,'Year'                          -- 피벗 필드

    ,'2006,2007,2008,2009,2010,2011' -- 피벗 값 리스트

    ,'CNT'                           -- 값 필드

    ,'DESC'                          -- 정렬 순서        ; 'ASC' 또는 'DESC'

    ,10;                             -- TOP 카운트       ; 0인 경우 미적용

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

 

■ SQL 집계하기

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

CREATE PROCEDURE [dbo].[SQLAggregate]

    @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 @SELECTList NVARCHAR(MAX);

    

    SET @SELECTList = '';

 

    -- 커서 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(@SELECTList) > 0

        BEGIN

            SET @SELECTList = @SELECTList + CHAR(13);

        END;

 

        SET @SELECTList = @SELECTList + '       ,SUM(CASE [' + @P_PivotField + '] WHEN ''' +

            @PivotField + ''' THEN [' + @P_ValueField + '] ELSE 0 END) AS [' + @PivotField + ']';

        

        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 *

FROM

(

    SELECT

        ' + @P_TargetFieldList + '

' + @SELECTList + '

       ,SUM([' + @P_ValueField + ']) AS TotalSum

    FROM

    (

        SELECT

            ' + @P_TargetFieldList + '

           ,' + @P_PivotField      + '

           ,' + @P_ValueField      + '

        FROM

        (

' + @P_SourceSQL + '

        ) AS A

    ) AS A

    GROUP BY ' + @P_TargetFieldList + '

) AS A

ORDER BY TotalSum ' + @P_SortOrder;

 

    EXECUTE SP_EXECUTESQL @SQL;

 

    SET ROWCOUNT 0;

    

    DROP TABLE #Pivot;

END

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

Posted by 사용자 icodebroker
TAG

댓글을 달아 주세요