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: 90304 More actions · Answer 1

You can review this thread: http://www.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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

SwePeso SSC-Dedicated Points: 39757 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: 1004406 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)