multiple rows to single row - top 3 results only

[afeind]

I have 2 tables - student table, contact table

Student table:
student_id (key)
fname
lname

Contact table:
student_id (key)
contact_id (key)
fname
lname
phone

Multiple contact records for each student id - max I have found is 10 records

student_id, phone
11111, 5551212
11111, 5553434
11111, 5558585
22222, 5555555
33333, 5555558
33333, 5555559
33333, 5555577
33333, 5555666

I need 1 row as follows:
student_id, phone1, phone2, phone3
11111, 5551212, 5553434, 5558585
22222, 5555555, null, null
33333, 5555558, 555559, 5555577

student 33333 has 4 records but I only need 3 results like above - I don't care what 3 but only 3. As stated some student_id's have as many as 10 contact phones.

Thanks in advance.

 

[markros]

Try:

;with cte as (select Student_ID, Phone, [Phone ] + cast(row_number() over (partition by Student_ID order by Phone) as varchar(10)) as Row from Contact)

select * from cte PIVOT (max(Phone) FOR Row IN ([Phone1],[Phone2],[Phone3])) pvt

PluralSight Learning Library

 

[afeind]

I am getting an error:

Msg 102, Level 15, State 1, Line3
Incorrect syntax near '('.

It doesn't like something below:

select * from cte PIVOT (max(Phone) FOR Row IN ([Phone1],[Phone2],[Phone3])) pvt

I am using SQL server 2008.

Thanks in advance

Adam

 

[markros]

I don't see an error in the posted code although I posted it from the top of my head.

If you just try the cte and

select * from cte,

does it work correctly?

PluralSight Learning Library

 

[TheBugSlayer]

How about

SELECT TOP 3 C.* FROM Contact C
JOIN Student S ON C.Student_ID = S.Student_ID
WHERE C.Student_ID = @StudentID

?

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)