撰寫TSQL來動態產生PIVOT語法，讓報表產出更有彈性

2015-08-22

1556
0
[SQL]TSQL語法技巧
2015-08-23

日前在PTT的Database版看見某位鄉民有個需求，需求如下

我有一個資料表A( ID, NAME)    <A1,A2,A3>
還有一個資料表B( ID, NAME)    <B1,B2,B3,B4,B5>
再來就是資料表C( ID, A的ID, B的ID, C數值)

我要怎麼下查詢才能夠
     B1   B2   B3   B4   B5
     -----------------------
A1 | C值 C值 0    0    C值
A2 | 0    0    0    0    0
A3 | 0    C值 0    C值 0

看見這樣的需求，第一個念頭就是用子查詢或pivot來做

因此自己就針對這樣需求來寫寫Code，並且筆記下來

我們先將基本環境及資料建立起來，如下圖所示。

Select出3個關聯的Table，確認一下我們填入的資料。

下圖中就是撰寫的Code，當然也可以用一句Query達到我們要的目的，但是個人覺得

如果能彈性的隨著A及B兩個Table的資料刪減來產出結果那就更好了。因此我的作法

就是寫成TSQL來動態串出語法。


Declare @Str Varchar(max);
Declare @Str2 Varchar(max);
Set @Str='';
Set @Str2='';
Select @Str=@Str +'IsNull(['+name+'],0) As '+name+',',@Str2=@Str2+'['+name+'],' From b;
Set @Str=Left(@Str,Len(@Str)-1);
Set @Str2=Left(@Str2,Len(@Str2)-1);
Exec('Select aname as Name,'+@Str+' From 
(Select a.name As aname,b.name As bname,c.value as value From a
Left Join c on c.aid=a.id
Left Join b on c.bid=b.id
) As TmpTb
Pivot
(Sum(value) For bname In('+@Str2+')) As PivotTb')