逗号分隔的ID列的值相同

问题描述：

我有很多解决方案可以通过逗号合并值。我有一些棘手的需求通过逗号合并id。我在桌上有大约10列。例如。逗号分隔的ID列的值相同

 declare @t1 TABLE (id INT, NAME VARCHAR(MAX), address1 varchar(max), mobileno varchar(max)); 
    INSERT @t1 values (1,'100',null,'1111111111'); 
    INSERT @t1 values (2,'100','pune','1111111111'); 
    INSERT @t1 values (3,'200',null,null); 
    INSERT @t1 values (4,'300','pune',null); 
    INSERT @t1 values (5,'200',null,'1111111111'); 

    SELECT 
    STUFF((
    SELECT ','+CAST(ID AS VARCHAR(10)) [text()] 
    FROM @t1 as B 
    WHERE B.NAME = A.NAME 
    FOR XML PATH('')),1,1,'') as ID, NAME, address1, mobileno 
    FROM @t1 as A 
    GROUP BY NAME, address1, mobileno

例如，

ID | Value 
---+------- 
1 | 100 
2 | 100 
3 | 200 
4 | 200 
5 | 300

我需要输出像

ID | Value 
---|------ 
1,2| 100 
3,4| 200 
    5| 300

这并不罕见。 Google：SQL Server字符串聚合。 –

答

--sample table: 
WITH ABC 
AS 
(
SELECT '1' as ID, '100' as value 

UNION 

SELECT '2' as ID, '100' as value 

UNION 

SELECT '3' as ID, '200' as value 

UNION 

SELECT '4' as ID, '200' as value 

UNION 

SELECT '5' as ID, '300' as value 


) 
--Expected query: 
SELECT 
STUFF((
SELECT ','+ID 
FROM ABC as B 
WHERE B.Value = A.Value 
FOR XML PATH('')),1,1,'') as ID, Value 
FROM ABC as A 
GROUP BY Value 

--Desired output: 
    ID Value 
    1,2 100 
    3,4 200 
    5 300

请注意：的','+ ID必须确保ID为VARCHAR或类似的字符串类型，否则就需要它正常工作之前转换。

逗号分隔的ID列的值相同

相关推荐