Consider a database with 3 tables holding names, with three rows:
names
ID,Name
1 , Eugene
2 , Igor
3 , George
rooms
ID, Roomnumber, Bednumber
1 , 10 , 1
1 , 15 , 1
1 , 10 , 2
2 , 5 , 1
2 , 3 , 1
Is there any way to get roomnumbers into single string with unique items only (can be separated by space or comma etc? So result will look something like this
ID, Name , Roomnumbers
1 , Eugene , 10 15
2 , Igor , 5 3
3 , George ,
I had no problem doing select from single table into same row, but I am having problem joining this tables together with distinc. Using samples that I found here the best I ever get is
ID, Name , Roomnumbers
1 , Eugene , 10 10 15
2 , Igor , 5 3
3 , George ,
The solution was
SELECT
distinct t1.ID, t1.Name,
SUBSTRING((SELECT ',' + CAST(t2.Roomnumber AS VARCHAR(10))
FROM (SELECT DISTINCT ID, Roomnumber FROM Table2) t2
WHERE t2.ID = t1.ID
ORDER BY t2.ID, t2.Roomnumber
FOR XML PATH ('')
), 2, 100) [Roomnumbers]
FROM Table1 t1
Thank you very much. I was trying to join and that was wrong and too complicated way to go around it. Thank you JPW
