[Solved]Pivot question - 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)

[Solved]Pivot question

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-20 : 11:49:31

I have a table which has a new row if the user has a second email address. Only a small number have 2 emails.
I want to pivot column Email into 2 columns Email_1 and Email_2.

I've tried using CASE. I can fill Email_2 but it shows an extra row as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:51:44

show your current query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:53:55

you could use something like below


SELECT  userid,
MAX(CASE WHEN Seq=1 THEN email ELSE NULL END) AS email_1,
MAX(CASE WHEN Seq=2 THEN email ELSE NULL END) AS email_2
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY userid ORDER BY emailfield) AS Seq,other fields here....
FROM Table
)t
GROUP BY userid

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-21 : 04:26:08

cheers that did it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:29:42

Simpler...

SELECT		userID,
		MIN(emailField) AS eMail1,
		NULLIF(MAX(emailField), MIN(emailField)) AS eMail2
FROM		Table1
GROUP BY	userID
ORDER BY	userID

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-21 : 07:08:24

cheers I tested both - in my I example I had to join to another few tables and with almost 3,000 rows being displayed.
Over a few runs Peso's solution was usually around 10 - 20 ms faster elapsed time and 10ms faster CPU time, but visakh16's solution was sometimes a little faster.

Thanks to both of you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 07:17:26

How did you join the other tables?
Directly or using the suggestions above as derived tables?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources