SELECT contactId, phonenumber from contact where contactId = 5
I would get say 2 numbers or perhaps 3 numbers or 4 etc.. in this format
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