Pivot with sum of multiple columns

Question

Post reply

Pivot with sum of multiple columns

tinausa

SSCrazy

Points: 2697
More actions
November 2, 2012 at 8:49 am

#393531

I have a table with product sales by region for three years - 2010-2012
tbl1
prod region year revenue
p1 r1 2010 100
p1 r1 2010 15
p1 r2 2011 150
p1 r3 2012 50
p2 r1 2010 200
p2 r2 2012 25
p3 r1 2011 300
p3 r1 2012 400
p3 r4 2012 50
I need to show the above data as below - with year as columns
prod region 2010 2011 2012
p1 r1 115 0 0
p1 r2 0 150 0
p1 r3 0 0 50
p2 r1 200 0 0
and so on..
I know how to pivot on sum of one column but modifying that for multiple columns give error.
Can anyone please help? Is there any other I can do this?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Charles Hearn SSCrazy Points: 2680 More actions · Answer 1

For much more information:

http://www.sqlservercentral.com/articles/T-SQL/63681/

I can give you some quick pivot operator code:

IF OBJECT_ID('TempDB..#TBL1') IS NOT NULL DROP TABLE #TBL1

CREATE TABLE #TBL1

(Prod CHAR(2) NOT NULL,

Region CHAR(2) NOT NULL,

YEAR INT NOT NULL,

Revenue INT NOT NULL)

INSERT INTO #TBL1

( Prod ,

Region ,

YEAR ,

Revenue

)

VALUES ('p1', 'r1', 2010, 100),

('p1', 'r1', 2010, 15),

('p1', 'r2', 2011, 150),

('p1', 'r3', 2012, 50),

('p2', 'r1', 2010, 200),

('p2', 'r2', 2012, 25),

('p3', 'r1', 2011, 300),

('p3', 'r1', 2012, 400),

('p3', 'r4', 2012, 50)

SELECT *

FROM (SELECT Prod, Region, YEAR, Revenue

FROM #TBL1) T

PIVOT (SUM(Revenue)

FOR Year IN ([2010], [2011], [2012])) P

ORDER BY Prod, Region

tinausa SSCrazy Points: 2697 More actions · Answer 2

Sorry but I could not make it to work this way.

Not sure what I am doing wrong.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Have you tried the Cross tabs method?

It's in the link provided by Charles. I find it much easier to understand and apply.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Charles Hearn SSCrazy Points: 2680 More actions · Answer 4

I suspect you are having problems with the table value constructors. Try what I have below.

IF OBJECT_ID('TempDB..#TBL1') IS NOT NULL DROP TABLE #TBL1

CREATE TABLE #TBL1

(Prod CHAR(2) NOT NULL,

Region CHAR(2) NOT NULL,

YEAR INT NOT NULL,

Revenue INT NOT NULL)

INSERT INTO #TBL1

( Prod ,

Region ,

YEAR ,

Revenue

)

SELECT 'p1', 'r1', 2010, 100

UNION ALL SELECT 'p1', 'r1', 2010, 15

UNION ALL SELECT 'p1', 'r2', 2011, 150

UNION ALL SELECT 'p1', 'r3', 2012, 50

UNION ALL SELECT 'p2', 'r1', 2010, 200

UNION ALL SELECT 'p2', 'r2', 2012, 25

UNION ALL SELECT 'p3', 'r1', 2011, 300

UNION ALL SELECT 'p3', 'r1', 2012, 400

UNION ALL SELECT 'p3', 'r4', 2012, 50

SELECT *

FROM (SELECT Prod, Region, YEAR, Revenue

FROM #TBL1) T

PIVOT (SUM(Revenue)

FOR Year IN ([2010], [2011], [2012])) P

ORDER BY Prod, Region

I would really recommend reading the article I linked to. There is a lot to cover but it will give you some good tools in your developer toolbox.

tinausa SSCrazy Points: 2697 More actions · Answer 5

Thanks you and sorry for delay in acknowledging.

I used cross tab to do what I needed.

tinausa SSCrazy Points: 2697 More actions · Answer 6

tinausa

SSCrazy

Points: 2697

November 15, 2012 at 11:23 am

#1559467

Thank you. Cross tab work.ed