Monday, March 26, 2012

MultiRow select to SingleRow output?

Here is what I want to do.

I have a customer table with say id, firstname, lastname. I also have another table called phone number that contains multiple phone numbers based on the customer id (id, customerid, phonenumber).

Ok I want to output a record like this

firstname, lastname, phone1, phone2, phone3

I need one record per customer with as many phone numbers as they may have, I could always limit the numbers to 3 or better yet return a null if there are less then the predefined number of phone numbers.

If I try a simple join I get multiline output which is no good for me.

SELECT firstname, lastname, phonenumber
FROM customer, phone
WHERE phone.customerid = customer.id

Thanks.---------------
create function dbo.PhoneString(@.customerid int)
returns varchar(500)
as
begin
declare @.PhoneString varchar(500)

set @.PhoneString = ''

select @.PhoneString = @.PhoneString + phonenumber + ', '
from PhoneNumberTable
where customerid = @.customerid

set @.PhoneString = left(@.PhonesString, len(@.PhoneString)-1)

return @.PhoneString
end
---------------
select firstname, lastname, dbo.PhoneString(ID) from customers

No comments:

Post a Comment