[DATABASE/SQLSERVER] GROUP BY-CASE문 : 동적 집계하기

■ GROUP BY-CASE 키워드를 이용해 동적 집계를 하는 방법을 보여준다.

 

▶ GROUP BY-CASE 키워드 : 동적 집계하기 예제 (SQL)

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인 경우 미적용

 

▶ GROUP BY-CASE 키워드 : 동적 집계하기 (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