Here is what you can do - write a couple of user defined functions and use the following technique to concatenate phone numbers and nicknames into a string:

    USE pubs
    DECLARE @StoreIDs varchar(8000)

    SELECT @StoreIDs = ISNULL(@StoreIds + ', ', '') + stor_id
    FROM sales
 
    SELECT @StoreIDs

Then call the functions from your query and pass in the customer ID:

Select nCus_id, dbo.GetPhoneNumbers(nCus_id), dbo.GetNickNames(nCus_id) From Customer

Watch out for performance issues because for each row you'll be running two subqueries, so make sure you have indexes on customer ID in each table. As long as you need to return only a few rows you should be OK.

Roman


>I have this problem.
>
>Let's say I have a customer table and I'm joining to a customer_phones table and also to an customer_nicknames table.
>
>Now I want the result to look like this, somehow combining the phone numbers into one field and combining the nicknames into another field, basically one row per customer.
>
>nCus_id: 10
>cPhones: '954-546-5496,954-549-6446'
>cNickNames: 'rico,tommy,rock'