sql - 连接两个表并连接单个字段的多行

Question

询问:

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

电流输出:

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

期望输出:

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

我想在键列 ID 上加入两个表 #Table1 和 #Tabl2，并从第二个表中获取值字段......但在一行中(如所需的输出所示)。

我如何(如果可能)实现所需的输出？

谢谢

Answer 1

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

对于 SQL Server 2017 及更高版本

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

结果集

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