使用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脚本：

CREATE TABLE [dbo].[T_Dept](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [DeptName] [nvarchar](10) NULL,

 CONSTRAINT [PK_T_Dept] PRIMARY KEY CLUSTERED

(

    [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[T_Emp](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [DeptId] [int] NULL,

    [EmpName] [nvarchar](10) NULL,

 CONSTRAINT [PK_T_Emp_1] PRIMARY KEY CLUSTERED

(

    [Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[T_Emp]  WITH CHECK ADD  CONSTRAINT [FK_T_Emp_T_Dept] FOREIGN KEY([DeptId])

REFERENCES [dbo].[T_Dept] ([Id])

GO

ALTER TABLE [dbo].[T_Emp] CHECK CONSTRAINT [FK_T_Emp_T_Dept]

GO

INSERT INTO [dbo].[T_Dept] SELECT ‘销售部‘      

INSERT INTO [dbo].[T_Dept] SELECT ‘研发部‘

INSERT INTO [dbo].[T_Dept] SELECT ‘人力资源部‘

INSERT INTO [dbo].[T_Dept] SELECT ‘行政部‘

INSERT INTO [dbo].[T_Dept] SELECT ‘财务部‘

INSERT INTO [dbo].[T_Emp] SELECT ‘1‘,‘Tom‘

INSERT INTO [dbo].[T_Emp] SELECT ‘1‘,‘John‘

INSERT INTO [dbo].[T_Emp] SELECT ‘2‘,‘Blue‘

INSERT INTO [dbo].[T_Emp] SELECT ‘2‘,‘Banks‘

INSERT INTO [dbo].[T_Emp] SELECT ‘3‘,‘Niki‘

INSERT INTO [dbo].[T_Emp] SELECT ‘3‘,‘Yuki‘

INSERT INTO [dbo].[T_Emp] SELECT ‘5‘,‘小明‘

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

原文：https://www.cnblogs.com/jn-shao/p/13991930.html