-1

I have a table called CARS(id,brand,owner). Lets assume that owner is the foreign key here and my data in the table is:

1   mercedes  Jack
2   mercedes  John
3   bmw       Jack
4   bmw       John
6   audi      Jack

What I want is to see an output like that:

1   mercedes, bmw, audi   Jack
2   mercedes, bmw         John

How can I manage this? I wrote this query below, but it is not working right. Any help would be appreciated.

select CARS.id, CARS.brand, CARS.owner
from CARS 
inner join (Select c1.id, (c1.brand + ',' + c2.brand) from CARS c inner join CARS c2 
           on (c1.owner = c2.owner) where c1.brand <> c2.brand) as tempCars
on (CARS.id = tempCars.id)

I couldnt manage the merge process correctly..

Edit: My post looks like similar to the post Tab Alleman mentioned. I saw it before asking this question, but the answer is not that clear for me so I still couldnt figure out how to modify my query to serve my objective. Any additional help would be appreciated.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • I saw this before posting my question but still couldnt manage to fix mine..:( – Eray Balkanli Feb 22 '16 at 14:26
  • The code that you have tried doesn't look anything like the accepted answer from the duplicate question. Please read it again and try again. The key parts are thr `CROSS JOIN` and using the `FOR XML`. – Tom H Feb 22 '16 at 14:32

1 Answers1

0

Try it like this:

DECLARE @tbl TABLE(id INT, brand VARCHAR(100),owner VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'mercedes','Jack')
,(2,'mercedes','John')
,(3,'bmw','Jack')
,(4,'bmw','John')
,(6,'audi','Jack');

WITH DistinctOwners AS
(
    SELECT DISTINCT owner 
    FROM @tbl
)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS inx
      ,STUFF((
        SELECT ',' + innerTbl.brand
        FROM @tbl AS innerTbl 
        WHERE innerTbl.owner=do.owner
        FOR XML PATH('')
       ),1,1,'') AS brands
       ,do.owner
FROM DistinctOwners AS do

The result

1   mercedes,bmw,audi   Jack
2   mercedes,bmw        John
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thank you very much, it is working. When you have some free time can u please explain me the logic of FOR XML path, how it is working? Have a nice day. – Eray Balkanli Feb 22 '16 at 14:38
  • @Eray, `FOR XML` is the syntax to get the output as XML. With `PATH` you state, that you take care of the XML element's and attribute's names yourself. And then you leave all of them empty... So poor SQL Server creates an XML without any element name - which is almost the result we want. The `STUFF()` function takes the first comma away. – Shnugo Feb 22 '16 at 14:42
  • Hi downvoter! Please explain why... Downvotes without comments are rather - uhm - weak... – Shnugo Feb 22 '16 at 15:06
  • he downvoted me as well. We have a troll ha :)) thanks for you quick reply and appreciated for your answer – Eray Balkanli Feb 22 '16 at 15:17