dynamic query

Question

hi
I am trying below query
the problem is when i execute the result of @Query without pivot clause I get the values of Balance4
however when I try to use Pivot Clause I get Null for Sum(Balance4)

what wrong I am doing ... Please help

DECLARE @Query Nvarchar(max)

DECLARE @pivotcolumns NVARCHAR(MAX)
declare @asondate datetime ='20180117'
select @pivotcolumns=coalesce(@pivotcolumns + ',',' ')+QUOTENAME(rtrim(Name)) from pbranches
set @Query = N' select PrdAcctId,PrdCd, ' + @pivotcolumns + '
into ##tempdb
FROM
(
select * from
( select c.Name,cast(a.LBrCode as varchar(5)) branch,a.PrdAcctId,substring(a.PrdAcctId,1,8) prd, b.PrdCd,Balance4 ,
rank () over (partition by a.LBrCode,a.PrdAcctId order by CblDate desc) rnk
from
cl_balances a inner join pmaster b on a.LBrCode=b.LBrCode and rtrim(substring(a.PrdAcctId,1,8))=rtrim(b.PrdCd)
inner join D001003 c on a.LBrCode=c.PBrCode
where convert(char(8),a.CblDate,112)<='+ convert(char(8),@asondate,112)+' and floor(substring(a.PrdAcctId,17,8))=0
)x where rnk=1
) SAVI
pivot
( sum(Balance4) for branch in (' + @pivotcolumns + ')) m'
select @Query mq
exec sp_executesql @Query

Answer 1

Before you start to build dynamic queries, write a static query with fixed columns so you get the syntax right. Then you can start to write dynamic queries.

I have some tips on crafting dynamic pivots here: http://www.sommarskog.se/dynamic_sql.html#pivot. You will find that I don't use the PIVOT operator...

Answer 2

Glad your problem is solved.
If you have any question, please feel free to let me know.

Regards
Echo