以 Northwind 資料庫為範例資料庫，實作之前請至 CodePlex 網站下載。
DECLARE @ColumnName NVARCHAR(MAX)= N'' --儲存動態欄位之用，宣告已初始化
--取得資料行列表
SELECT @ColumnName = @ColumnName + iif(@ColumnName = N'',QUOTENAME(LastName),N',' + QUOTENAME(LastName))
FROM (SELECT DISTINCT (LastName) FROM Employees) temp
或是
DECLARE @ColumnName as NVARCHAR(MAX) --儲存動態欄位之用，宣告未初始化
--取得資料行列表
Select @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(LastName)
from (select distinct LastName from Employees) temp order by LastName
DECLARE @ColumnName NVARCHAR(MAX)= N'' --儲存動態欄位之用，宣告已初始化
--取得資料行列表
SELECT @ColumnName = @ColumnName + iif(@ColumnName = N'',QUOTENAME(LastName),N',' + QUOTENAME(LastName))
FROM (SELECT DISTINCT (LastName) FROM Employees) temp
或是
DECLARE @ColumnName as NVARCHAR(MAX) --儲存動態欄位之用，宣告未初始化
--取得資料行列表
Select @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(LastName)
from (select distinct LastName from Employees) temp order by LastName
接著下來可以將樞紐資料行的清單加到 SQL字串變數中，然後用 sp_executesql 執行
--動態組合SQL字串且使用sp_executesql來執行
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @ColumnName + '
FROM
(
select c.LastName,sum(b.Quantity * b.UnitPrice) Amount
from Orders a
join [Order Details] b
on a.OrderID = b.OrderID
join Employees c
on a.EmployeeID = c.EmployeeID
group by c.EmployeeID,c.LastName
) AS j
PIVOT
(
SUM(Amount)
FOR LastName
IN ('+ @ColumnName + ')
) AS t'
PRINT @sql
EXEC sp_executesql @sql
若您要使用只需將資料行列表的子查詢以及PIVOT所需的來源查詢，替換成符合您的情境之查詢指令碼即可輕鬆的使用 PIVOT 關係運算子所帶來的便利。
與with配合使用範例 :
;
With EmpNTTotal
As
(
SELECT case when [OCCUR_DATE] is null then year([ISSUE_DATE]) else year([OCCUR_DATE]) End 年度
,year([ISSUE_DATE]) as ISSUE_YEAR
,[EMPLOYEE_NO]
,[EMPLOYEE_CNAME]
,[DEPT_CODE]
,[DEPT_CNAME]
,[BIG_ITEM_CNAME]
,[OCCUR_DATE]
,[NodeName]
,[state]
,sum([APPLY_MONEY]) 目前已申請金額
FROM [BPMDEV].[dbo].[BENEFIT_PROCESSED]
where EMPLOYEE_NO IS NOT NULL
and NodeName ='結束'
and state='簽核完成'
group by year([ISSUE_DATE]),[EMPLOYEE_NO],[EMPLOYEE_CNAME],[DEPT_CODE],[DEPT_CNAME],[BIG_ITEM_CNAME],[OCCUR_DATE],[NodeName],[state]
)
SELECT *
FROM EmpNTTotal
PIVOT
(
SUM(目前已申請金額) FOR BIG_ITEM_CNAME IN ([生育禮金], [新居落成禮金], [住院慰問], [員工或眷屬傷喪亡奠儀], [自費進修補助],[子女(本人)獎學金], [結婚禮金]
)) PivotTable
