Join two tables and concatenate multiple rows of a single field

Query:

CREATE TABLE #Temp1 (ID int)
CREATE TABLE #Temp2 (ID int, Value varchar(10))

INSERT INTO #Temp1 (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3

INSERT INTO #Temp2(ID, Value)
SELECT 1,'One-One'
UNION ALL
SELECT 1,'One-Two'
UNION ALL
SELECT 1,'One-One'
UNION ALL
SELECT 2,'Two-One'
UNION ALL
SELECT 2,'Two-Two'
UNION ALL
SELECT 2,'Two-One'

SELECT T1.ID, T2.Value FROM #Temp1 T1 INNER JOIN #Temp2 T2 ON T1.ID = T2.ID

DROP TABLE #Temp1
DROP TABLE #Temp2

Current Output:

ID  Value
1   One-One
1   One-Two
1   One-One
2   Two-One
2   Two-Two
2   Two-One

Desired Output:

ID  Values
1,  One-One, One-Two, One-One
2,  Two-One, Two-Two, Two-One

I would like to join two tables, #Table1 and #Tabl2, on a key column, ID, and get Value field from the 2nd table… but in a single row (as shown in desired output).

How (if possible) can I achieve the Desired Output?

Thank you

Answer

SELECT T1.ID,
      STUFF((SELECT ', ' + T2.Value
             FROM   #Temp2 T2 
             WHERE  T1.ID = T2.ID
             FOR XML PATH(''),TYPE)
             .value('.','NVARCHAR(MAX)'),1,2,'') AS Value
FROM #Temp1 T1 
GROUP BY T1.ID

For SQL Server 2017 and Later Versions

SELECT T1.ID, STRING_AGG(T2.Value , ', ') AS [Value]
FROM #Temp1 T1 
INNER JOIN #Temp2 T2 ON T1.ID = T2.ID
GROUP BY T1.ID

Result Set

╔════╦═══════════════════════════╗
║ ID ║           Value           ║
╠════╬═══════════════════════════╣
║  1 ║ One-One, One-Two, One-One ║
║  2 ║ Two-One, Two-Two, Two-One ║
╚════╩═══════════════════════════╝