我有以下代码,我试图用零替换使用枢轴时出现的 Null.我做了以下,但它说'ISNULL'附近的语法不正确."我不确定我做错了什么?大家有什么建议
I have the following code and I am trying to replace the Null that appear when using the pivot with zero. I do the following but it says that "Incorrect syntax near 'ISNULL'." I am not sure what I am doing wrong? Any suggestions please
select *
from #tempfinaltable
pivot ISNULL(sum(TotalXSAAL),0) for Section_desc in
([Communication],[Construction],[Energy],[Financial Institutions],
[General Property],[HIGHER ED & HEALTHCARE],
[Inland Marine],[Real Estate])) AS AALs
与我使用的动态 SQL 相同.上面的查询只是显示了名字,所以你可以看到我正在使用什么
The same the dyanmic SQL I am using. The above query is just shows the names so you can see what I am working with
select *
from #tempfinaltable
pivot (sum(TotalXSAAL) for Section_desc in
' + '('+@BranchNames++')) AS AALs'
你能告诉我这句话有什么问题吗?我遇到了语法问题:
Can you tell me what's wrong with this statement. i am having a syntax issue:
BEGIN
Set @ISNullBranchNames = @ISNullBranchNames + 'ISNULL('+(@BranchNames+',0),'
Set @BranchNames = @BranchNames + '['+@BranchName+'],'
FETCH NEXT FROM CUR1 INTO @BranchName
END
所有PIVOT
子句都必须放在括号内.
All PIVOT
clause must be in bracket.
结果查询必须如下所示:
The result query must look like this:
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 演示
更新
如何生成部分动态 SQL.
How to generate parts of dynamic 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