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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment