pivot multiple columns

Question

pivot multiple columns

mvee

Hall of Fame

Points: 3634
More actions
June 23, 2008 at 5:50 pm

#188406

i have a one-column pivot working.
eg.
normalized table:
site date group (g) page position
a 1/1 1 1 5
a 1/1 2 2 6
b 1/1 1 3 7
b 1/1 2 4 8
pivoted:
site date g1_page g2_page
a 1/1 1 2
b 1/1 3 4
what i want is:
site date g1_page g2_page g1_pos g2_pos
a 1/1 1 2 5 6
b 1/1 3 4 7 8
can anybody give me an example of how to add the second column pivot?
thanks for any help.
-Megan

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

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

You can review this thread: http://qa.sqlservercentral.com/Forums/Topic521489-338-1.aspx

How you proceed will all depend upon how you are going to calculate each value. Are they all SUM's or is one a SUM and the other a COUNT? If the latter, you need to use the cross-tab type or use two pivots. Based upon the testing, I would recommend using the cross-tab version since it performs much better.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 2

DECLARE@Sample TABLE

(

Site CHAR(1),

[Date] VARCHAR(5),

[Group] INT,

Page INT,

Position INT

)

INSERT@Sample

SELECT'a', '1/1', 1, 1, 5 UNION ALL

SELECT'a', '1/1', 2, 2, 6 UNION ALL

SELECT'b', '1/1', 1, 3, 7 UNION ALL

SELECT'b', '1/1', 2, 4, 8

SELECTSite,

[Date],

MAX(CASE WHEN [Group] = 1 THEN Page ELSE NULL END) AS g1_Page,

MAX(CASE WHEN [Group] = 2 THEN Page ELSE NULL END) AS g2_Page,

MAX(CASE WHEN [Group] = 1 THEN Position ELSE NULL END) AS g1_Position,

MAX(CASE WHEN [Group] = 2 THEN Position ELSE NULL END) AS g2_Position

FROM@Sample

GROUP BYSite,

[Date]

ORDER BYSite,

[Date

N 56°04'39.16"
E 12°55'05.25"

mvee Hall of Fame Points: 3634 More actions · Answer 3

Thanks guys. I'll probably go with the crosstab option, even though I really have 7 groups and 4 columns to pivot. I hate how you have to spell everything out instead of having dynamic column names...

fyi - all pivot columns will have the same aggregate function.

Thanks for the replies.

-Megan

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

So, write some dynamic SQL to do it...

--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)
Intro to Tally Tables and Functions