MSSQL PIVOT, UNPIVOT

 

MSSQL PIVOT, UNPIVOT 예제

 pivot기능과 unpivot 기능을 모르면 쿼리 작성시 노가다성 쿼리를 많이들 짜게 되는데요.
(case문을 이용한 방법이나, 커서를 돌리는 방법)
아래와같이 PIVOT, UNPIVOT 기능을 숙지하고 있으면 많이 편해지겠죠?? ㅎㅎ

// 예제 테이블을 만듭니다.
CREATE TABLE Sales (
CityID int,
MonthName char(3) CONSTRAINT Sales_MonthName_Inlist CHECK (MonthName IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')),
Amount decimal(20,2),
CONSTRAINT PK_Sales PRIMARY KEY CLUSTERED (CityID, MonthName)
)

// 아래는 pivot 되어진 데이타를 insert 시킬 테이블입니다.
CREATE TABLE SalesPivoted (
CityID int PRIMARY KEY CLUSTERED,
Jan decimal(20,2),
Feb decimal(20,2),
Mar decimal(20,2),
Apr decimal(20,2),
May decimal(20,2),
Jun decimal(20,2),
Jul decimal(20,2),
Aug decimal(20,2),
Sep decimal(20,2),
Oct decimal(20,2),
Nov decimal(20,2),
Dec decimal(20,2)
)

-- 예제 테이블에 데이타를 만들어 넣어줍니다.
SET NOCOUNT ON
DECLARE @CityID int
SET @CityID = 1
WHILE @CityID <= 10000 BEGIN
INSERT Sales
SELECT @CityID, MonthName, Amount FROM (
SELECT MonthName = 'Jan', Amount = Round(Rand() *10000, 2) UNION ALL SELECT 'Feb', Round(Rand() *10000, 2) UNION ALL SELECT 'Mar', Round(Rand() *10000, 2) UNION ALL SELECT 'Apr', Round(Rand() *10000, 2) UNION ALL SELECT 'May', Round(Rand() *10000, 2) UNION ALL SELECT 'Jun', Round(Rand() *10000, 2) UNION ALL SELECT 'Jul', Round(Rand() *10000, 2) UNION ALL SELECT 'Aug', Round(Rand() *10000, 2) UNION ALL SELECT 'Sep', Round(Rand() *10000, 2) UNION ALL SELECT 'Oct', Round(Rand() *10000, 2) UNION ALL SELECT 'Nov', Round(Rand() *10000, 2) UNION ALL SELECT 'Dec', Round(Rand() *10000, 2)
) X
SET @CityID = @CityID + 1
END

-- 예제테이블에 데이타를 pivot 시켜 SalesPivoted 테이블에 insert 합니다.
-- pivot 명령은 mssql 호환성 90 이상부터 작동하니, 아래 쿼리 실행시 에러 나면 호환성을 확인해주세요.
INSERT SalesPivoted
SELECT PivotData.*
FROM Sales
PIVOT (Sum(Amount) FOR MonthName IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])) PivotData
GO

-- unpivot 방법은 mssql2000과 mssql2005에따라 방법이 다릅니다.
-- MSSQL 2000로 unpivoting ( 이방법은 호환성 90이하여도 실행이 됩니다. 참고하시길...)
SELECT * FROM (
SELECT
             CityID,
             MonthName,
             Amount = CASE MonthName
                               WHEN 'Jan' THEN [Jan]
                               WHEN 'Feb' THEN [Feb]
                               WHEN 'Mar' THEN [Mar]
                               WHEN 'Apr' THEN [Apr]
                               WHEN 'May' THEN [May]
                               WHEN 'Jun' THEN [Jun]
                               WHEN 'Jul' THEN [Jul]
                               WHEN 'Aug' THEN [Aug]
                               WHEN 'Sep' THEN [Sep]
                               WHEN 'Oct' THEN [Oct]
                               WHEN 'Nov' THEN [Nov]
                               WHEN 'Dec' THEN [Dec]
                               ELSE 'Unknown'
                           END
FROM  SalesPivoted
           CROSS JOIN (
                                SELECT MonthName = 'Jan' UNION ALL SELECT 'Feb' UNION ALL

                     SELECT 'Mar' UNION ALL SELECT 'Apr' UNION ALL

                     SELECT 'May' UNION ALL SELECT 'Jun' UNION ALL

                                                  SELECT 'Jul' UNION ALL SELECT 'Aug' UNION ALL

                                                  SELECT 'Sep' UNION ALL SELECT 'Oct' UNION ALL

                                                  SELECT 'Nov' UNION ALL SELECT 'Dec'
                               ) M
) X WHERE Amount < 0

GO

-- MSSQL  2005 unpivoting ( 이방법은 호환성 90이상에서만 실행됩니다.)
SELECT * FROM (
SELECT CityID, MonthName, Amount
FROM
   SalesPivoted
   UNPIVOT ( Amount FOR MonthName IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug],

                                                        [Sep], [Oct], [Nov], [Dec]) ) As UnpivotData
) X WHERE Amount < 0
GO