[MS SQL] 使用PIVOT 做兩個欄位的交叉分析(動態欄位)

上一篇 :[MS SQL] 使用PIVOT 做兩個欄位的交叉分析(固定欄位) 說明了如何將固定欄位轉成交叉分析表, 此篇進一步說明如何讓動態欄位轉成交叉分析表。

1. 產生範例資料表，這次我增加了一個交通工具選項(竹蜻蜓)，目的是要說明交通工具欄位可以是動態的。
這邊我建立一個資料表值函數(Function)，回傳範例資料表。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION uf_Table1()
RETURNS 
@Table1 TABLE 
(
 WhereToGo nvarchar(10), --要去哪?
 HowToGo nvarchar(10), --怎麼去?
 TotCnt int    --多少人選此方法
)
AS
BEGIN
 INSERT INTO @Table1 
 SELECT '台北','開車',2 UNION  SELECT '台北','坐高鐵',9 UNION  SELECT '台北','竹蜻蜓',200 
 UNION  SELECT '高雄','開車',5 UNION  SELECT '高雄','坐高鐵',10 UNION  SELECT '高雄','竹蜻蜓',234 

 RETURN;
END
GO

SELECT * FROM dbo.uf_Table1();

2. 在使用PIVOT產生交叉分析表之前，我們要先產生一個動態的欄位字串。

--產生一個動態的欄位字串。
DECLARE @columnNames nvarchar(MAX);

SELECT @columnNames = COALESCE(@columnNames + ',', '') + QUOTENAME(HowToGo)
FROM dbo.uf_Table1()
GROUP BY HowToGo

SELECT @columnNames;

3. 使用PIVOT產生動態欄位的交叉分析表:

DECLARE @SQL nvarchar(MAX);

SET @SQL = '';
SET @SQL = @SQL + 'SELECT * FROM dbo.uf_Table1() '
SET @SQL = @SQL + 'PIVOT( '
SET @SQL = @SQL + ' SUM(TotCnt) ' 
SET @SQL = @SQL + ' FOR HowToGo IN (' + @columnNames + ') '
SET @SQL = @SQL + ') AS pvt '
SET @SQL = @SQL + 'ORDER BY pvt.WhereToGo ';

EXEC(@SQL);--執行SQL

請試著增加交通工具欄位的值，看看會有什麼結果。




[註1]
QUOTENAME： 傳回Unicode字串，且附加了分隔符號，以便使輸入字串成為有效的SQL Server分隔識別碼。SQL Server 2005(含)以上才支援，詳細內容請參考這裡。

DECLARE @columnName nvarchar(10);
SET @columnName = '姓名欄位';

SELECT QUOTENAME(@columnName);--沒加引數2, 則預設是用中括號"[" 
SELECT QUOTENAME(@columnName,'('); --> (姓名欄位)
SELECT QUOTENAME(@columnName,'{'); --> {姓名欄位}
SELECT QUOTENAME(@columnName,'<'); --> <姓名欄位>
SELECT QUOTENAME(@columnName,''''); --> '姓名欄位'
SELECT QUOTENAME(@columnName,'"'); --> "姓名欄位"
SELECT QUOTENAME(@columnName,':'); --> NULL

[註2]
COALESCE(欄位1, 欄位2)：兩個欄位，選不是NULL的那個輸出。

DECLARE @Table1 TABLE(
 CellPhone nvarchar(10),
 Tel nvarchar(10)
)

INSERT INTO @Table1
SELECT '0910123***','022292****' UNION 
SELECT '0978***232',NULL UNION 
SELECT NULL, '022292****'  UNION 
SELECT NULL,NULL;

SELECT * FROM @Table1;

--利用COALESCE產生連絡電話欄位
SELECT COALESCE(CellPhone,Tel) AS '連絡電話' 
FROM @Table1