PIVOT、UNPIVOT

◎ PIVOT

第一種方式

DECLARE @HouseSoldRecord1 TABLE
(
    SalesName NVARCHAR(100) ,
    Area NVARCHAR(100) ,
    Price MONEY
);

INSERT  @HouseSoldRecord1 VALUES  ( N'Name01', N'Area02', 400000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name02', N'Area01', 500000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area01', 560000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name02', N'Area02', 350000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area02', 440000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area03', 460000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area03', 470000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name02', N'Area01', 330000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name01', N'Area01', 470000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area03', 320000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name01', N'Area01', 390000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name02', N'Area02', 350000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area03', 430000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name02', N'Area03', 440000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area02', 450000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area01', 475000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area02', 489000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name02', N'Area02', 399000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name01', N'Area03', 499000 );
INSERT  @HouseSoldRecord1 VALUES  ( N'Name03', N'Area01', 520000 );

--根據不同地區做分組加總
SELECT  Area01 AS '內湖區'
        , Area02 AS '中山區'
        , Area03 AS '文山區'
FROM (SELECT Area, Price from @HouseSoldRecord1) A PIVOT
( 
  SUM(Price) 
  FOR Area IN ( Area01, Area02, Area03 ) --這裡必須放 Area 欄位裡面有出現過的值，可以選擇性放幾個即可
) AS PivotTable

--根據 Sales、Area 做分組加總
SELECT  SalesName AS '銷售人員'
        , Area01 AS '內湖區'
        , Area02 AS '中山區'
        , Area03 AS '文山區'
FROM (SELECT SalesName, Area, Price from @HouseSoldRecord1) B PIVOT
( 
  SUM(Price) 
  FOR Area IN ( Area01, Area02, Area03) --這裡必須放 Area 欄位裡面有出現過的值，可以選擇性放幾個即可
) AS PivotTable
UNION ALL
SELECT  'Total'
        , Area01 AS '內湖區'
        , Area02 AS '中山區'
        , Area03 AS '文山區'
FROM (SELECT Area, Price from @HouseSoldRecord1) A PIVOT
( 
  SUM(Price) 
  FOR Area IN ( Area01, Area02, Area03 ) --這裡必須放 Area 欄位裡面有出現過的值，可以選擇性放幾個即可
) AS PivotTable

第二種方式

CREATE TABLE HouseSold
(
    SalesName NVARCHAR(100) ,
    Area NVARCHAR(100) ,
    Price MONEY
);

INSERT  HouseSold VALUES  ( N'Name01', N'Area02', 400000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area01', 500000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area01', 560000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area02', 350000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area02', 440000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 460000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 470000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area01', 330000 );
INSERT  HouseSold VALUES  ( N'Name01', N'Area01', 470000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 320000 );
INSERT  HouseSold VALUES  ( N'Name01', N'Area01', 390000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area02', 350000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 430000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area03', 440000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area02', 450000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area01', 475000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area02', 489000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area02', 399000 );
INSERT  HouseSold VALUES  ( N'Name01', N'Area03', 499000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area01', 520000 );

--把 AREA 欄位裡面所有出現過的值用 GROUP BY 之後變成一個 @變數
DECLARE @TEMPTEXT NVARCHAR(MAX) = '';
SELECT @TEMPTEXT += ',' + QUOTENAME(Area)
FROM (SELECT AREA FROM HouseSold GROUP BY AREA) A
--去掉第一個逗點
SET @TEMPTEXT = SUBSTRING(@TEMPTEXT, 2, LEN(@TEMPTEXT) - 1)
--動態產生SQL，把 @變數 丟進去 FOR ()裡
DECLARE @SQL NVARCHAR(MAX) = '';
SET @SQL = 'SELECT  Area01 AS 內湖
        , Area02 AS 中山
        , Area03 AS 文山
                , '''' --用這種方式的「''」一定要在左右兩側再加「'」
FROM (SELECT Area, Price from HouseSoldRecord1) A PIVOT
( 
  SUM(Price) 
  FOR Area IN ( ' + @TEMPTEXT + ')
) AS PivotTable';
--執行SQL
EXEC sys.sp_executesql @SQL

◎ UNPIVOT

CREATE TABLE HouseSold
(
    SalesName NVARCHAR(100) ,
    Area NVARCHAR(100) ,
    Price MONEY
);

INSERT  HouseSold VALUES  ( N'Name01', N'Area02', 400000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area01', 500000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area01', 560000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area02', 350000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area02', 440000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 460000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 470000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area01', 330000 );
INSERT  HouseSold VALUES  ( N'Name01', N'Area01', 470000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 320000 );
INSERT  HouseSold VALUES  ( N'Name01', N'Area01', 390000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area02', 350000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area03', 430000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area03', 440000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area02', 450000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area01', 475000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area02', 489000 );
INSERT  HouseSold VALUES  ( N'Name02', N'Area02', 399000 );
INSERT  HouseSold VALUES  ( N'Name01', N'Area03', 499000 );
INSERT  HouseSold VALUES  ( N'Name03', N'Area01', 520000 );

--製作PIVOT表, INSERT至另外一張TABLE
SELECT  SalesName
        , Area01 AS '內湖'
        , Area02 AS '中山'
        , Area03 AS '文山'
INTO HouseSold2
FROM (SELECT SalesName, Area, Price from HouseSoldRecord1) A PIVOT
( 
  SUM(Price) 
  FOR Area IN ([Area01],[Area02],[Area03])
) AS PivotTable

--UNPIVOT
SELECT SalesName AS '銷售人員'        
       , Area456 AS '區'
       , PRICE123 as '售價'
FROM (SELECT SalesName,內湖,中山,文山 FROM HouseSold2) a UNPIVOT
(
    PRICE123
    FOR Area456 IN (內湖,中山,文山)
) AS UnPivotTable

results matching ""

    No results matching ""