在 SQL SERVER 中使用 FOR XML PATH 和 STUFF 将行值连接为逗号分隔的字符串

在 SQL SERVER 中使用 FOR XML PATH 和 STUFF 将行值连接为逗号分隔的字符串

简介：在本文中，将分享如何在 SQL SERVER 中使用 FOR XML PATH() 和 STUFF 将表中单个列的多个行值转换或连接为单个查询中的逗号分隔字符串。

实例：为了演示目的，让我们创建一些临时表和查询。

创建一个临时表来存储员工数据，并在该表中插入一些虚拟数据以用于演示目的

CREATE TABLE #tbEmployee 
(
  EmployeeId INT IDENTITY(1,1) PRIMARY KEY , 
  EmployeeName VARCHAR(50)
)

INSERT INTO #tbEmployee (EmployeeName) 
VALUES
('AMAN'), 
('RAMAN'),
('ANUJ'),
('KABEER'),
('AAMIR')

检查插入的数据
从#tbEmployee中选择*

使用 FOR XML PATH 和 STUFF 查询以逗号分隔的字符串形式获取所有员工。

SELECT STUFF((SELECT DISTINCT ‘, ’ + CAST(EmployeeName AS VARCHAR(100)) FROM #tbEmployee FOR XML PATH(’‘)),1,2,’') AS Employees

注意：这里我们使用 FOR XML PATH 将行值连接为逗号分隔的字符串，并使用 STUFF 命令获取第一个字符，在我们的例子中是逗号，并将其替换为空

所以查询结果将是：

现在让我们让这个例子更有用。假设需要以逗号分隔的形式显示所有员工及其资格。有多种方法可以获得所需的结果。其中一种方法是使用 FOR XML PATH。让我们通过示例了解如何获得所需的结果。

让我们创建一个主表来存储资格，并使用下面的脚本在其中插入一些资格。

CREATE TABLE #tbQualification  
(
 QualificationId INT IDENTITY(1,1) PRIMARY KEY ,
 QualificationName  VARCHAR(50)
)

INSERT INTO  #tbQualification (QualificationName) 
VALUES
('MCA'), 
('M.TECH'),
('B.TECH'),
('BCA'),
('MBA')

Check inserted qualifications
SELECT * FROM #tbQualification
Query result:

现在创建一个表来存储员工的资格。

CREATE TABLE #tbEmployeeQualifications 
(
  Id INT IDENTITY(1,1) PRIMARY KEY , 
  EmployeeId INT, 
  QualificationId INT
)

INSERT INTO  #tbEmployeeQualifications (EmployeeId,QualificationId) 
VALUES
(1,4),
(1,5),
(2,2), 
(2,3),
(3,4),
(3,1), 
(3,5),
(5,4);

查询以获取员工及其资格

SELECT E.EmployeeId,E.EmployeeName, Q.QualificationName FROM #tbEmployee E
LEFT OUTER JOIN #tbEmployeeQualifications EQ ON E.EmployeeId=EQ.EmployeeId

LEFT OUTER JOIN #tbQualification Q ON Q.QualificationId=EQ.QualificationId

Query Result:

正如我们所看到的，如果任何员工具有多种资格，则有多行。

但是假设我们想为每个具有多个资格的员工显示单个记录作为逗号分隔的字符串，那么我们可以将查询编写为：

查询以获取每个员工的多个资格作为逗号分隔的字符串：

SELECT E.EmployeeId,E.EmployeeName,
    STUFF
    (
        (
            SELECT ', ' + CAST(Q.QualificationName AS VARCHAR(50))
            FROM #tbEmployeeQualifications EQ
            LEFT OUTER JOIN #tbQualification Q ON Q.QualificationId=EQ.QualificationId
            WHERE EQ.EmployeeId = E.EmployeeId
            ORDER BY Q.QualificationName
            FOR XML PATH('')
        ), 1, 2, ''
    ) AS Qualifications
FROM
    #tbEmployee E

Query Result: