SQL Server 동적 PIVOT Query

[2018-08-30 목요일]

* 내용: 동적 PIVOT 사용방법

1. 쿼리

2. 결과

 

 

1. 쿼리

---

* PIVOT 처리할 데이터

- #TOTAL_RESULT 테이블 ROWS
- 월별(MM)로 집계

 

* 쿼리

-- 결과 PIVOT 
DECLARE @COLUMNS      NVARCHAR(MAX)
DECLARE @QUERY        NVARCHAR(MAX)
 
SET @COLUMNS = ''
 
SELECT @COLUMNS = @COLUMNS + '[' + MM + '], '
FROM ( 
    SELECT DISTINCT YYYY, MM 
    FROM #CALENDAR
) AS CAL
ORDER BY YYYY, MM
 
SET @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1)
 
PRINT @COLUMNS
 
SET @QUERY = '
        SELECT ' + @COLUMNS + '
        FROM (
            SELECT MM
                , CONVERT(VARCHAR, CONVERT(
                    MONEY
                    , RESULT_AMT
                ) , 1) AS RESULT_AMT
            FROM (
                SELECT DISTINCT 
                    EMP_ID        
                    , GUBUN        
                    , CONVERT(INT, YYYY)    AS YYYY
                    , CONVERT(INT, MM)        AS MM
                    --, ROUND(CONVERT(FLOAT, SUM(RESULT_AMT) OVER ( PARTITION BY GUBUN, THIS) ) / 100000000, 0) AS RESULT_AMT
                    , CONVERT(NUMERIC(12,2), SUM(RESULT_AMT) OVER ( PARTITION BY YYYY, MM) ) / 100 AS RESULT_AMT
                FROM #TOTAL_RESULT
            ) AS FINAL_RESULT
        ) AS RESULT
        PIVOT (MAX(RESULT_AMT) FOR MM IN ('+ @COLUMNS +')) AS A
    '
--PRINT @QUERY
EXEC (@QUERY)
 

2. 결과

---

 

* 내용