logo头像

Edward.K Thinking

SQL Pivot 動態陳述式

很多報表需求在輸出時候常需要做欄位轉置,雖然,這動作可以在.Net做,也可以在T-SQL做,至於,兩者如何取其一,將必須視當時情境決定。

自從SQL2005後已經內建Pivot功能,SQL Pivot基本用法,不過,說真的用了MS SQL Pivot Function感覺還有點陽春,只能針對兩個欄位的其中一個欄位進行轉置,一個欄位固定,這樣往往不敷企業需求,在企業報表都是會有多個固定欄位而針對一個欄位做轉置,直接使用Pivot語法就會有點虛。因此,為了針對企業需求,可以動態指定轉置欄位且可以多個固定不轉置欄位,所以,需強化原本Pivot功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create PROCEDURE [dbo].[SP_Pivot]( 
@SourceTable varchar(max),
@Pivot_Fix_Column varchar(5000),
@Pivot_Value_Aggregate varchar(10),
@Pivot_Value_Column varchar(5000),
@Pivot_Column_List varchar(5000))
AS
BEGIN

declare @columns varchar(max)
declare @sql nvarchar(max)

set @sql = N'set @columns = substring((select '', [''+convert(varchar,'+@Pivot_Column_List+')+'']'' from '+@SourceTable +' group by '+@Pivot_Column_List+' order by 1 for xml path('''')),2,8000)'

execute sp_executesql @sql,
N'@columns varchar(max) output',
@columns=@columns output

set @sql = N'SELECT * FROM
(SELECT '+@Pivot_Fix_Column+','+@Pivot_Column_List+','+@Pivot_Value_Column+' from '+@SourceTable+') src
PIVOT
('+@Pivot_Value_Aggregate+'('+@Pivot_Value_Column+') FOR '+@Pivot_Column_List+' IN ('+@columns+') ) pvt
ORDER BY 1'

execute sp_executesql @sql

END
輸入參數說明
  • SourceTable(需要轉置的資料表或是資料語法)
  • Pivot_Fix_Column(轉置後要呈現的欄位)
  • Pivot_Value_Aggregate(轉置同時要用的運算,example:sum,max…etc)
  • Pivot_Value_Column(對應到需要轉置欄位的value,only column)
  • Pivot_Column_List(需轉置欄位,only column)
使用範例
1
EXEC dbo.Sp_dynamicpivot '(select a,b,day,Qty from tmpA) a',' a,b','sum','QTY','Day'
上一篇