Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Add sum to this dynamic sql pivot.

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-02-12 : 00:14:46
Hi.
I have created a pivot dynamically but i also want to add a sum on qtt (is integer) that i cannot do it.If i put a sum after 'SELECT [description],' + @cols + ' , it will say qtt column not found, if i group by after "x" and before pivot is still does not work.
Any thoughts?


DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME([date])
from Table_1
group by [date]
order by [date]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')


set @query = 'SELECT [description],' + @cols + '
from
(
SELECT
a.[description],
a.[date],
a.qtt
-- ,sum(a.qtt) as total
FROM Table_1 a
group by [date],[description],qtt
) x
pivot
(
max(qtt)
for [date] in (' + @cols + ')
) p '

execute sp_executesql @query;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 00:20:15
i think you should use this method instead if you want to use multiple pivot

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-02-12 : 01:23:39
I'll have a look tomorrow.Does this support sum of columns?
Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 05:26:11
quote:
Originally posted by sapator

I'll have a look tomorrow.Does this support sum of columns?
Thanks.


yes it will

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -