sqlserver字符串多行合并为一行
时间：2021-07-01 10:21:17
帮助过：41人阅读
--
创建测试表
2 CREATE TABLE [dbo].[TestRows2Columns](
3 [Id] [
int] IDENTITY(
1,
1) NOT NULL,
4 [UserName] [nvarchar](
50) NULL,
5 [Subject] [nvarchar](
50) NULL,
6 [Source] [numeric](
18,
0) NULL
7 )
8 GO
9
10 --
插入测试数据
11 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source])
12 SELECT N
‘张三‘,N
‘语文‘,
60 UNION ALL
13 SELECT N
‘李四‘,N
‘数学‘,
70 UNION ALL
14 SELECT N
‘王五‘,N
‘英语‘,
80 UNION ALL
15 SELECT N
‘王五‘,N
‘数学‘,
75 UNION ALL
16 SELECT N
‘王五‘,N
‘语文‘,
57 UNION ALL
17 SELECT N
‘李四‘,N
‘语文‘,
80 UNION ALL
18 SELECT N
‘张三‘,N
‘英语‘,
100
19 GO
20
21 SELECT *
FROM [TestRows2Columns]
22
23
24
25
26 --
1 通过
select 累加
27 DECLARE @sql_col VARCHAR(
8000)
28 SELECT @sql_col = ISNULL(@sql_col +
‘,‘,
‘‘) +
QUOTENAME([Subject]) FROM TestRows2Columns
29 GROUP BY [Subject]
30
31 SELECT @sql_col
32
33
34
35
36 --
2 通过 FOR xml path(
‘‘) 合并字符串记录
37 SELECT
38 STUFF(
39 (SELECT
‘#‘ +
Subject
40 FROM TestRows2Columns
41 WHERE UserName =
‘王五‘
42 FOR xml path(
‘‘)
43 ),
1,
1,
‘‘
44 )
45
46
47 --
3 分组合并字符串记录
48 SELECT
49 UserName,
50 Subject =
(
51 STUFF(
52 (SELECT
‘#‘ +
Subject
53 FROM TestRows2Columns
54 WHERE UserName =
A.UserName
55 FOR xml path(
‘‘)
56 ),
1,
1,
‘‘
57 )
58 )
59 FROM TestRows2Columns A
60 GROUP by UserName
sqlserver字符串多行合并为一行
标签：eric 测试表 arc 行合并 style row union all entity from
