How to use pivot - 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)

How to use pivot

Author

Topic

jhermiz

3564 Posts

Posted - 2010-01-07 : 09:59:12

Simple example, 2 tables: Login, Phone

A login has multiple phone numbers such as work, home

So imagine in one table you have

Jon

And in the phone table you have 2 phone numbers:

Work 222-2222
Home 333-3333

If I do this:

SELECT l.FullName, p.PhoneType, p.PhoneNumber FROM Login l INNER JOIN Phone p ON p.LoginID = l.LoginID

It gives me

Jon Work 222-2222
Jon Home 333-3333

Nice..but I need it all on one row so I tried using Pivot (mind you its the first time I've ever used it. So I tried this:

SELECT FullName, [Phone1] AS Phone1, [Phone2] AS Phone2 FROM
(SELECT l.FullName, p.PhoneType, p.PhoneNumber FROM Login l INNER JOIN Phone p ON p.LoginID = l.LoginID) ps
PIVOT
(
MAX(ps.PhoneNumber)
FOR
ps.PhoneType IN ([Phone1], [Phone2])
) AS pvt

And it displays

Jon NULL NULL

Apparently I am not using pivot correctly ?

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:01:56

you should give actual type values inside


SELECT FullName, [Work], [Home] FROM
(SELECT l.FullName, p.PhoneType, p.PhoneNumber FROM Login l INNER JOIN Phone p ON p.LoginID = l.LoginID) ps
PIVOT
(
MAX(ps.PhoneNumber)
FOR
ps.PhoneType IN ([Work], [Home])
) AS pvt

jhermiz

3564 Posts

Posted - 2010-01-07 : 10:06:56

quote:

Originally posted by visakh16

you should give actual type values inside


SELECT FullName, [Work], [Home] FROM
(SELECT l.FullName, p.PhoneType, p.PhoneNumber FROM Login l INNER JOIN Phone p ON p.LoginID = l.LoginID) ps
PIVOT
(
MAX(ps.PhoneNumber)
FOR
ps.PhoneType IN ([Work], [Home])
) AS pvt

Ok that did it...I guess my next question is what happens when you don't know exactly how many types there will be ? For instance, in this case I knew there was work and home, but what happens when there are so many different variations?

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:08:47

then you need to do this dynamically

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

jhermiz

3564 Posts

Posted - 2010-01-07 : 10:22:55

quote:
Originally posted by visakh16

then you need to do this dynamically

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Thank you for that
I will read it when I get a chance :)

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:36:21

ok..let us know if you face any difficulty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources