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)

Pivot ?

Author

Topic

spakowski
Starting Member

2 Posts

Posted - 2010-04-27 : 11:09:36

I have a 'phones' table similar to below:

phoneID__Filenumber____phonenumber
1________1234________1234567
2________1234________1235678
3________5678________5678901
4________5678________2345664
5________5678________5647488
6________4567________6783562

I want to Join with our master table (master.number = phones.number) so that I can display an account name with respective phone numbers within a single row. (like below)

name_____filenumber___phone1___phone2____phone3
John______1234_______1234567__1235678
Bob_______5678_______5678901__2345664____5647488
Sam_______4567_______6783562

As you can see every filenumber represents one account (ex. John).
I was trying to figure out if I could achieve this through the use of PIVOT or a CASE statement or some other way....?

Any suggestions or ideas would be greatly appreciated.

Thanks,
Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 11:16:58

[code]
SELECT m.name,n.filenumber,n.phone1,n.phone2,n.phone3
FROM master m
JOIN
(
SELECT Filenumber,
MAX(CASE WHEN Seq=1 THEN phonenumber ELSE NULL END) AS phone1,
MAX(CASE WHEN Seq=2 THEN phonenumber ELSE NULL END) AS phone2,
MAX(CASE WHEN Seq=3 THEN phonenumber ELSE NULL END) AS phone3
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Filenumber ORDER BY phoneID) AS Seq,*
FROM Table
)t
GROUP BY Filenumber
)n
ON n.filenumber = m.number
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 11:18:56

You'll need to know at design time the maximum number of phones a user has. OR, you could use dynamic SQL.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

spakowski
Starting Member

2 Posts

Posted - 2010-04-27 : 11:30:12

Fantastic! Thanks visakh16, that's exactly what I was looking for!!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 12:33:24

You also can use concatenating rows for the requirement very easy like this:


SELECT M.name, M.filenumber,
(SELECT phonenumber + ' ,'
FROM phones
WHERE filenumber = M.filenumber
ORDER BY phonenumber
FOR XML PATH('')) AS phone_list
FROM Master M

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 12:35:26

quote:
Originally posted by ms65g

You also can use concatenating rows for the requirement very easy like this:

SELECT M.name, M.filenumber,
(SELECT phonenumber + ' ,'
FROM phones
WHERE filenumber = M.filenumber
ORDER BY phonenumber
FOR XML PATH('')) AS phone_list
FROM Master M

I'd do that, as it will work with a varying number of phone numbers per person.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:37:12

quote:
Originally posted by spakowski

Fantastic! Thanks visakh16, that's exactly what I was looking for!!

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources