Sum pivot columns

Question

Post reply

Sum pivot columns

MTY-1082557

SSCarpal Tunnel

Points: 4540
More actions
May 13, 2010 at 3:08 pm

#140274

How can I sum 2 pivot columns?
this is my sample. you can copy and past it on SQL.
I need to sum the columns and have a 3rd column with the total.
anyone??
CREATE TABLE Test
(
idINT
,idNamevarchar(32)
, ZONEVARCHAR(32)
, SCount int
)
INSERT test VALUES (1, 'Row 1', 'Zone1', 10)
INSERT test VALUES (1, 'Row 1', 'Zone2', 5)
INSERT test VALUES (2, 'Row 2', 'Zone1', 10)
INSERT test VALUES (2, 'Row 2', 'Zone2', 5)
INSERT test VALUES (3, 'Row 3', 'Zone1', 10)
INSERT test VALUES (3, 'Row 3', 'Zone2', 5)
SELECT * FROM Test
DECLARE @Columns VARCHAR(8000)
,@Columns2 VARCHAR(8000)
,@Sql VARCHAR(4000)
SET @Columns = substring((select distinct ',['+Zone+']' from Test group by Zone for xml path('')),2,8000)
SET @Columns2 = substring((select distinct ',IsNull(['+Zone+'],0) as ['+Zone+']' from Test group by Zone for xml path('')),2,8000)
SET @SQL = 'SELECT idName, '+@Columns2+'
FROM
(Select Id, IdName
, Zone, SCount from Test) SourceData
PIVOT
(sum(SCount) for Zone in ('+@Columns+')) pivottable
Order by Id '
exec(@sql)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would the following help?

I'm using OVER (PARTITION BY) to get the total per idName.

Side note: Since you already using dynamic SQL you might want to have a look at the DynamicCrossTab article referenced in my signature. It might perform better than PIVOT....;-)

SET @SQL = 'SELECT idName, '+@Columns2+', total

FROM

(Select Id, IdName,SUM(Scount) OVER (PARTITION BY idName) as total

, Zone, SCount from Test) SourceData

PIVOT

(sum(SCount) for Zone in ('+@Columns+')) pivottable

Order by Id '

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

MTY-1082557 SSCarpal Tunnel Points: 4540 More actions · Answer 2

Thank you Lutz

It works !

I will take at look at your article.

thank you again

LutzM SSC Guru Points: 107049 More actions · Answer 3

MTY-1082557 (5/14/2010)
Thank you Lutz
It works !
I will take at look at your article.
thank you again

My pleasure 😀

As a side note: the article I referenced in my signature is not mine. It's all Jeffs fault!! :hehe:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)