在SQL中使用stuff()将多个值连接到单个列 [英] Concatenate multiple values to a single column using stuff() in SQL

问题描述

ID  | NAME  
----|--------  
1   |Ann  
2   |Jake  
1   |Julie  
3   |Paul  
2   |Shane  
4   |Kumi

我想使用stuff()连接值，如下所示.并且单个值不应受stuff()函数的影响.

I want to concatenate values using stuff() as below. And the single values should not be affected by the stuff() function.

ID  | NAME  
----|--------  
1   |Ann,Julie  
2   |Jake,Shane  
3   |Paul  
4   |Kumi

该怎么做?

推荐答案

CREATE TABLE #A
(ID INT, NAME VARCHAR(10))
INSERT INTO #A VALUES
(1,'ANN'),
(2,'JAKE'),
(1,'JULIE'),
(3,'PAUL'),
(2,'SHANE'),
(4,'KUMI')

SELECT DISTINCT ID ,
STUFF((SELECT ','+NAME
FROM #A T1
WHERE T1.ID=T2.ID
FOR XML PATH('')),1,1,'') FROM #A T2

这篇关于在SQL中使用stuff()将多个值连接到单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！