need help - 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

General SQL Server Forums

New to SQL Server Programming

need help

Author

Topic

morikoth
Starting Member

5 Posts

Posted - 2012-01-16 : 08:15:49

Hello,

I have a table like that:
ID phonenumber contact
1 29349245934 a
2 34953405823 a
3 34538458345 a
4 93459345394 b
5 54688456854 b
6 56786587856 c
7 54684568456 c
8 34858129349 c

I want to write a script which return me that table
Contact PhoneNB1 PhoneNB2 PhoneNB3
a 29349245934 34953405823 34538458345
b 93459345394 54688456854
c 54684568456 34858129349

How can i do that?i tried with while and pivot but it takes a long time to run. Any other ideas?

Thx,
Marius

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 10:04:00

[code]
SELECT contact,
MAX(CASE WHEN Seq=1 THEN phonenumber END) AS PhoneNB1,
MAX(CASE WHEN Seq=2 THEN phonenumber END) AS PhoneNB2,
MAX(CASE WHEN Seq=3 THEN phonenumber END) AS PhoneNB3
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY contact ORDER BY ID) AS Seq
FROM Table
)t
GROUP BY contact
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources