SQL Server列相加合并,字串相加 - Rowset string concatenation

复制范例代码，SQL指令执行即可。
Copy and paste to SQL command and execute.
范例一 Sample 1

1. -- Prepare sample data
   
2. 
   
3. DECLARE        @Sample TABLE (ID INT, Code VARCHAR(3))
   
4. 
   
5. INSERT        @Sample
   
6. 
   
7. SELECT        290780, 'LT' UNION ALL
   
8. 
   
9. SELECT        290780, 'AY' UNION ALL
   
10. 
    
11. SELECT        290781, 'ILS' UNION ALL
    
12. 
    
13. SELECT        290780, 'AY'
    
14. 
    
15. -- Show the expected output
    
16. 
    
17. SELECT DISTINCT        s1.ID,
    
18. 
    
19. STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
    
20. 
    
21. FROM         @Sample AS s1
    
22. 
    
23. ORDER BY        s1.ID
    
24. 
    
25. SELECT DISTINCT        s1.ID,
    
26. 
    
27. STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
    
28. 
    
29. FROM         @Sample AS s1
    
30. 
    
31. ORDER BY        s1.ID
    
32. 
    
33. SELECT DISTINCT        s1.ID,
    
34. 
    
35. STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
    
36. 
    
37. FROM         @Sample AS s1
    
38. 
    
39. ORDER BY        s1.ID

复制代码

范例二 Sample 2

1. --1. 创建表，添加测试数据
   
2. CREATE TABLE tb(id int, [value] varchar(10))
   
3. INSERT tb SELECT 1, 'aa'
   
4. UNION ALL SELECT 1, 'bb'
   
5. UNION ALL SELECT 2, 'aaa'
   
6. UNION ALL SELECT 2, 'bbb'
   
7. UNION ALL SELECT 2, 'ccc'
   
8. 
   
9. --SELECT * FROM tb
   
10. /**//*
    
11. id          value
    
12. ----------- ----------
    
13. 1           aa
    
14. 1           bb
    
15. 2           aaa
    
16. 2           bbb
    
17. 2           ccc
    
18. 
    
19. (5 row(s) affected)
    
20. */
    
21. 
    
22. 
    
23. --2 在SQL2000只能用自定义函数实现
    
24. ----2.1 创建合并函数fn_strSum，根据id合并value值
    
25. GO
    
26. CREATE FUNCTION dbo.fn_strSum(@id int)
    
27. RETURNS varchar(8000)
    
28. AS
    
29. BEGIN
    
30.     DECLARE @values varchar(8000)
    
31.     SET @values = ''
    
32.     SELECT @values = @values + ',' + value FROM tb WHERE id=@id
    
33.     RETURN STUFF(@values, 1, 1, '')
    
34. END
    
35. GO
    
36. 
    
37. -- 调用函数
    
38. SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id
    
39. DROP FUNCTION dbo.fn_strSum
    
40. 
    
41. ----2.2 创建合并函数fn_strSum2，根据id合并value值
    
42. GO
    
43. CREATE FUNCTION dbo.fn_strSum2(@id int)
    
44. RETURNS varchar(8000)
    
45. AS
    
46. BEGIN
    
47.     DECLARE @values varchar(8000)   
    
48.     SELECT @values = isnull(@values + ',', '') + value FROM tb WHERE id=@id
    
49.     RETURN @values
    
50. END
    
51. GO
    
52. 
    
53. -- 调用函数
    
54. SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id
    
55. DROP FUNCTION dbo.fn_strSum2
    
56. 
    
57. 
    
58. --3 在SQL2005中的新解法
    
59. ----3.1 使用OUTER APPLY
    
60. SELECT *
    
61. FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(
    
62.         SELECT [values]= STUFF(REPLACE(REPLACE(
    
63.             (
    
64.                 SELECT value FROM tb N
    
65.                 WHERE id = A.id
    
66.                 FOR XML AUTO
    
67.             ), '<N value="', ','), '"/>', ''), 1, 1, '')
    
68. )N
    
69. 
    
70. ----3.2 使用XML
    
71. SELECT id, [values]=STUFF((SELECT ','+[value] FROM tb t WHERE id=tb.id FOR XML PATH('')), 1, 1, '')
    
72. FROM tb
    
73. GROUP BY id
    
74. 
    
75. --4 删除测试表tb
    
76. drop table tb
    
77. 
    
78. /**//*
    
79. id          values
    
80. ----------- --------------------
    
81. 1           aa,bb
    
82. 2           aaa,bbb,ccc
    
83. 
    
84. (2 row(s) affected)
    
85. */

复制代码