SQL- row values for same id as list (,)

Question

I have table as this:

id  |  code
111 |  IL
111 |  IN
222 |  UK
222 |  IN

I want this:

id  |  path
111 |  IL,IN
222 |  UK,IN

and furher that to count the distinct ids in different paths, thanks

Answer 1

In SQL-Server you can do in following:

SAMPLE DATE

DECLARE @Tbl TABLE(id INT, code VARCHAR(40))
INSERT INTO @Tbl VALUES (111,'IL'),(111,'IN'),(222,'UK'),(222,'IN')

---

QUERY

SELECT  id
       ,STUFF((SELECT ',' + CAST(code AS VARCHAR(10)) [text()]
               FROM @Tbl 
               WHERE id = t.id
               FOR XML PATH(''), TYPE)
       .value('.','NVARCHAR(MAX)'),1,1,' ') path
FROM @Tbl t
GROUP BY id

---

OUTPUT

id   path
111  IL,IN
222  UK,IN