All PIVOT
子句必须位于括号内。
结果查询必须如下所示:
SELECT
'TotalXSAAL' as Col,
ISNULL([Communication], 0) AS [Communication],
ISNULL([Construction], 0) AS [Construction],
...,
...,
...
FROM #tempfinaltable
PIVOT
(
SUM(TotalXSAAL) for
Section_desc in
(
[Communication],[Construction],[Energy],[Financial Institutions],
[General Property],[HIGHER ED & HEALTHCARE],
[Inland Marine],[Real Estate]
)
)AS AALs
SQL FIDDLE 演示 http://sqlfiddle.com/#!3/82630/5
UPDATE
如何生成部分动态 SQL。
DECLARE @ISNullBranchNames nvarchar(MAX) = N'' -- you mast add empty string first, otherwise you will get NULL inresult
DECLARE @BranchNames nvarchar(MAX) = N''
.....
BEGIN
Set @ISNullBranchNames =
@ISNullBranchNames + 'ISNULL([' + @BranchName + '], 0) AS [' + @BranchName +'], '
Set @BranchNames = @BranchNames + '['+@BranchName+'],'
FETCH NEXT FROM CUR1 INTO @BranchName
END