June 23, 2008 at 5:50 pm
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
June 23, 2008 at 8:55 pm
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
June 24, 2008 at 1:03 am
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"
June 24, 2008 at 9:08 am
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
June 24, 2008 at 3:44 pm
So, write some dynamic SQL to do it...
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply