从MSSQL一行拆分成多行的技巧

1. 前言

当我们需要处理MSSQL中的数据时，有时需要将一行数据拆分成多行，这是一种比较常见的需求。本文将介绍如何在MSSQL中实现将一行拆分成多行的方法。

2. 实现方法

2.1 字符串拆分

实现将一行拆分成多行的最基本方法是使用字符串拆分函数。在MSSQL中，我们可以使用内置的STRING_SPLIT函数来实现字符串的拆分。

STRING_SPLIT函数的语法如下：

SELECT value FROM STRING_SPLIT(string, separator)

其中，string参数是要拆分的字符串，separator参数是分隔符（可以是一个或多个字符）。该函数将返回一列名为“value”的表，其中包含拆分后的所有子串。

下面是一个示例：

SELECT * FROM STRING_SPLIT('apple,orange,banana,mango', ',')

执行上述SQL语句后，将返回以下结果：

value

-----
apple
orange
banana
mango

我们可以应用类似的方法来将一行数据拆分成多行。假设我们有以下表：

CREATE TABLE Employees

(
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Departments VARCHAR(200)
)
GO
INSERT INTO Employees VALUES (1, 'John Doe', 'Sales,Marketing')
INSERT INTO Employees VALUES (2, 'Jane Smith', 'Sales,Finance')
INSERT INTO Employees VALUES (3, 'Bob Brown', 'Marketing')

我们想要将Departments字段的值拆分成多行，并将每个子元素与父元素（即Employee的每一行）关联。此时，我们可以使用CROSS APPLY语句结合STRING_SPLIT函数来实现。

SELECT e.Id, e.Name, d.value AS Department

FROM Employees e
CROSS APPLY STRING_SPLIT(e.Departments, ',') d

执行上述SQL语句后，将返回以下结果：

Id  Name        Department

------------------------------
1   John Doe    Sales
1   John Doe    Marketing
2   Jane Smith  Sales
2   Jane Smith  Finance
3   Bob Brown   Marketing

通过上述方法，我们成功地将Departments字段中的每个子元素拆分成了单独的行，并将其与相应的Employee关联起来。

2.2 XML方式

另一种将一行数据拆分成多行的方法是使用XML类型。在MSSQL中，我们可以通过将一行数据转换为XML格式来实现这一目标，具体步骤如下：

使用FOR XML PATH('')将一行数据转换为XML格式

使用XML方法节点（nodes()）将XML数据转换回关系型数据

下面是一个示例：

SELECT Id, Name, Departments,

    CAST('' 
         + REPLACE(Departments, ',', '') 
         + '' AS XML) AS DepartmentXml
FROM Employees

执行上述SQL语句后，将返回以下结果：

Id  Name        Departments        DepartmentXml

-------------------------------------------------
1   John Doe    Sales,Marketing    <departments><department>Sales</department><department>Marketing</department></departments>
2   Jane Smith  Sales,Finance      <departments><department>Sales</department><department>Finance</department></departments>
3   Bob Brown   Marketing          <departments><department>Marketing</department></departments>

DepartmentXml列中的值是一个XML节点，我们可以使用XML方法节点将其转换回关系型数据。具体做法如下：

SELECT Id, Name, d.value('department[1]', 'nvarchar(100)') AS Department

FROM Employees e
CROSS APPLY DepartmentXml.nodes('/departments/department') t(d)

执行上述SQL语句后，将返回以下结果：

Id  Name        Department

----------------------------
1   John Doe    Sales
1   John Doe    Marketing
2   Jane Smith  Sales
2   Jane Smith  Finance
3   Bob Brown   Marketing

通过上述方法，我们同样将Departments字段中的每个子元素拆分成了单独的行，并将其与相应的Employee关联起来。

3. 总结

本文介绍了在MSSQL中将一行数据拆分成多行的两种方法，即使用字符串拆分函数和XML方式。通过这些技巧，我们可以更方便地处理MSSQL中的数据。值得注意的是，使用XML方式可以提供更高的灵活性和可扩展性，但同时也会增加一定的复杂性。