使用FOR XML PATH实现多行数据合并成一列

　　有时为避免循环操作数据库、列表展示等一些原因需要将数据及关联数据批量加载进行集中处理，一种解决办法可以使用FOR XML PATH将多行数据合并成一列，达到字段拼接的效果。例如有两个表，

　　部门表T_Dept：

　　

　　员工表T_Emp：

　　

　　需要查询每个部门下的员工姓名，查询语句：

 1 SELECT ROW_NUMBER() OVER (ORDER BY DeptId) AS Row, DeptName
 2     , LEFT(A.EmpName, LEN(A.EmpName) - 1) AS EmpName
 3 FROM (
 4     SELECT DeptId, EmpName = (
 5             SELECT EmpName + ','
 6             FROM T_Emp emp2
 7             WHERE emp2.DeptId = emp1.DeptId
 8             FOR XML PATH('')
 9         )
10     FROM T_Emp emp1
11     GROUP BY emp1.DeptId
12 ) A
13     LEFT JOIN T_Dept dept ON dept.Id = A.DeptId
View Code

　　结果集：

 

 

　　也可以这样写：

 1 SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Row, DeptName
 2     , LEFT(A.EmpName, LEN(A.EmpName) - 1) AS EmpName
 3 FROM (
 4     SELECT Id, DeptName, EmpName = (
 5             SELECT EmpName + ','
 6             FROM T_Emp emp1
 7             WHERE emp1.DeptId = dept.Id
 8             FOR XML PATH('')
 9         )
10     FROM T_Dept dept
11 ) A
View Code

　　结果集:

　　

 

 

　　SQL脚本：

 1 CREATE TABLE [dbo].[T_Dept](
 2 
 3     [Id] [int] IDENTITY(1,1) NOT NULL,
 4 
 5     [DeptName] [nvarchar](10) NULL,
 6 
 7  CONSTRAINT [PK_T_Dept] PRIMARY KEY CLUSTERED
 8 
 9 (
10 
11     [Id] ASC
12 
13 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
14 
15 ) ON [PRIMARY]
16 
17 GO
18 
19  
20 
21 CREATE TABLE [dbo].[T_Emp](
22 
23     [Id] [int] IDENTITY(1,1) NOT NULL,
24 
25     [DeptId] [int] NULL,
26 
27     [EmpName] [nvarchar](10) NULL,
28 
29  CONSTRAINT [PK_T_Emp_1] PRIMARY KEY CLUSTERED
30 
31 (
32 
33     [Id] ASC
34 
35 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
36 
37 ) ON [PRIMARY]
38 
39 GO
40 
41  
42 
43 ALTER TABLE [dbo].[T_Emp]  WITH CHECK ADD  CONSTRAINT [FK_T_Emp_T_Dept] FOREIGN KEY([DeptId])
44 
45 REFERENCES [dbo].[T_Dept] ([Id])
46 
47 GO
48 
49  
50 
51 ALTER TABLE [dbo].[T_Emp] CHECK CONSTRAINT [FK_T_Emp_T_Dept]
52 
53 GO
54 
55  
56 
57 INSERT INTO [dbo].[T_Dept]
58 SELECT '销售部'
59 UNION ALL
60 SELECT '研发部'
61 UNION ALL
62 SELECT '人力资源部'
63 UNION ALL
64 SELECT '行政部'
65 UNION ALL
66 SELECT '财务部'
67 
68  
69 
70 INSERT INTO [dbo].[T_Emp]
71 SELECT '1', 'Tom'
72 UNION ALL
73 SELECT '1', 'John'
74 UNION ALL
75 SELECT '2', 'Blue'
76 UNION ALL
77 SELECT '2', 'Banks'
78 UNION ALL
79 SELECT '3', 'Niki'
80 UNION ALL
81 SELECT '3', 'Yuki'
82 UNION ALL
83 SELECT '5', '小明'
View Code

 

posted @ 2020-11-16 23:35  JN-SHao  阅读(275)  评论(0)    收藏  举报