Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Red face Unanswered: 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 =


  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    create function dbo.PhoneString(@customerid int)
    returns varchar(500)
    declare @PhoneString varchar(500)

    set @PhoneString = ''

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

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

    return @PhoneString
    select firstname, lastname, dbo.PhoneString(ID) from customers
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts