WOW... that is an amazing trick. Very cool. I will definatly try to not forget this one.


>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'