Ok, say I have this statement

SELECT contactId, phonenumber from contact where contactId = 5

I would get say 2 numbers or perhaps 3 numbers or 4 etc.. in this format

5, 3439849
5, 4938495
5, 3928291

What I want is to get this instead

5, 3439849, 4938495, 3928291

Actually what I want is a bit more compicated, I want this structure

contactId, phone1, phone2, phone3, phone4, phone5

If say the contact has only 2 phones it should return

5, 3439849, 4938495,,,,

Basically the idea is to return a record set which is then exported to a text file containing the contact's id and up to 5 phone numbers associated with them for importation into another program. The final output of the file would be this.


This I can do with DTS mapping each row of the returned query to a single line in a file. So really I just need to return the row properly in my SELECT. Someone suggested a function that returns the numbers.

SELECT contactId, dbo.GetPhoneNumbers(contactId) from contact where contactId = 5

But how do I write the function?