uncategorized

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'
Share