help with PIVOT operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with PIVOT operator

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-01 : 14:57:03

Hi,

Just got my query working from a different thread. I've isolated the mess from the other thread so just creating a new thread for this to make it easier. Hopefully this is clean and simple.

My query is currently bringing back my desired results in a format like this.

referDate / page / totalreferrers

20080801 1 10
20080801 10 1
20080801 12 1
20080801 14 1
20080801 16 2
20080801 2 4
20080801 20 1
20080801 25 1
20080801 27 1
20080801 3 1
20080801 30 1
20080801 5 3
20080801 6 2
20080801 7 1
20080801 8 1
20080801 9 1
20080731 1 18
20080731 11 1
20080731 12 2
20080731 14 4
20080731 16 2
20080731 18 2
20080731 2 11
20080731 20 3
20080731 21 2
20080731 22 2
20080731 23 1
20080731 25 2
20080731 29 3
20080731 3 6

Ive been playing with the PIVOT command for a bit, but unable to get it going. I keep ending up with all 0's in the fields or having multiple date colunms.

I want to PIVOT these results so I have a result set that is in a format like :

referDate,page1,page2,page3,page4,page5,(keep goin to column20)
20080801, 1 , 2 , 2 , 1 , 2 , etc..
20080731, 1 , 1 , 9 , 3 , 9 , etc..
20080730, 4 , 3 , 7 , 1 , 4 , etc..
20080729, 1 , 2 , 2 , 4 , 1 , etc..

My syntax or logic seems to be a bit off..

Is something obviously wrong in this query? I'm pretty stumped. Its bringing back all 0's, with multiple repeat dates.

Thanks again for all the help!

mike123

SELECT p.referDate,
p.Page1,
p.Page2,
p.Page3,
p.Page4,
p.Page5,
p.Page6,
p.Page7,
p.Page8,
p.Page9,
p.Page10,
p.Page11,
p.Page12,
p.Page13,
p.Page14,
p.Page15,
p.Page16,
p.Page17,
p.Page18,
p.Page19,
p.Page20,
p.Page21,
p.Page22,
p.Page23,
p.Page24,
p.Page25
FROM (

SELECT CONVERT(varchar(10), land.queryDate, 112) as referDate,thePage ,COUNT(*) AS TotalReferrers

FROM tbl2 as land

GROUP BY CONVERT(varchar(10), land.queryDate, 112), thepage

) AS s
PIVOT (
COUNT(thepage)
FOR thepage IN ([page1], [Page2], [Page3], [Page4], [Page5], [Page6], [Page7], [Page8], [Page9], [Page10], [Page11], [Page12], [Page13], [Page14], [Page15], [Page16], [Page17], [Page18], [Page19], [Page20], [Page21], [Page22], [Page23], [Page24], [Page25])
) AS p

ORDER BY referDate DESC

robc
Yak Posting Veteran

60 Posts

Posted - 2008-08-01 : 15:48:27

Too hard.

p.s hi mike.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-11 : 04:43:39

bump for if anyone can help on this one, still stuck...

much appreciated as always!

thx,
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-11 : 05:03:16

[code]DECLARE @sample TABLE
(
referDate datetime,
page int,
totalreferrers int
)

INSERT INTO @sample
SELECT '20080801', 1, 10 UNION ALL
SELECT '20080801', 10, 1 UNION ALL
SELECT '20080801', 12, 1 UNION ALL
SELECT '20080801', 14, 1 UNION ALL
SELECT '20080801', 16, 2 UNION ALL
SELECT '20080801', 2, 4 UNION ALL
SELECT '20080801', 20, 1 UNION ALL
SELECT '20080801', 25, 1 UNION ALL
SELECT '20080801', 27, 1 UNION ALL
SELECT '20080801', 3, 1 UNION ALL
SELECT '20080801', 30, 1 UNION ALL
SELECT '20080801', 5, 3 UNION ALL
SELECT '20080801', 6, 2 UNION ALL
SELECT '20080801', 7, 1 UNION ALL
SELECT '20080801', 8, 1 UNION ALL
SELECT '20080801', 9, 1 UNION ALL
SELECT '20080731', 1, 18 UNION ALL
SELECT '20080731', 11, 1 UNION ALL
SELECT '20080731', 12, 2 UNION ALL
SELECT '20080731', 14, 4 UNION ALL
SELECT '20080731', 16, 2 UNION ALL
SELECT '20080731', 18, 2 UNION ALL
SELECT '20080731', 2, 11 UNION ALL
SELECT '20080731', 20, 3 UNION ALL
SELECT '20080731', 21, 2 UNION ALL
SELECT '20080731', 22, 2 UNION ALL
SELECT '20080731', 23, 1 UNION ALL
SELECT '20080731', 25, 2 UNION ALL
SELECT '20080731', 29, 3 UNION ALL
SELECT '20080731', 3, 6

SELECT *
FROM @sample s
pivot
(
SUM(totalreferrers)
FOR page IN ([1], [2], [3], [4], [5])
) p[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 06:02:33

Didn't we cover this issue here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107775
with

SELECT	p.landDate,
	p.Page1,
	p.Page2,
	p.Page3,
	p.Page4,
	p.Page5,
	p.Page6,
	p.Page7,
	p.Page8,
	p.Page9,
	p.Page10
FROM	(
		SELECT	landDate,
			'Page' + PARSENAME(REPLACE(landingPage, '_', '.'), 2) AS thePage
		FROM	tblLandingPages
	) AS s
PIVOT	(
		COUNT(thePage)
		FOR thePage IN ([Page1], [Page2], [Page3], [Page4], [Page5], [Page6], [Page7], [Page8], [Page9], [Page10])
	) AS p

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

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-08-11 : 08:05:12

Hi Khtan,

Perfect. got it thank you.

Peso - we did address the query there and we got it going great, i was just unable to follow the PIVOT part exactly and integrating it into my query was confusing. Using the temp table I was able to better understand what was going on exactly and got everything going perfectly now.

thanks to both of you!! much appreciated

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources