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

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

100

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 카운트

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

GROUP BY ' + @P_TargetFieldList + '

) AS A

ORDER BY TotalSum ' + @P_SortOrder;

EXECUTE SP_EXECUTESQL @SQL;

SET ROWCOUNT 0;

DROP TABLE #Pivot;

END