SQL Server 中，将单行数据转换为多行数据

时间：2024-10-27 07:41:21

        在 SQL Server 中，将单行数据转换为多行数据通常涉及到将某个字段中的逗号分隔的值拆分成多行。这种操作通常称为“拆分”或“展开”（Explode）。以下是一些常用的方法来实现这一目标：

1. 使用内置函数 STRING_SPLIT

从 SQL Server 2016 开始，提供了 STRING_SPLIT 函数来方便地拆分字符串。

示例

        假设你有一个表 T，其中有一列 Values 包含逗号分隔的字符串，你想要将这些字符串拆分成多行。

CREATE TABLE T (Id INT, Values VARCHAR(255));
INSERT INTO T (Id, Values) VALUES (1, 'A,B,C,D');

SELECT *
FROM T
CROSS APPLY STRING_SPLIT(T.Values, ',');

  2. 使用自定义函数

        如果你使用的是早期版本的 SQL Server（如 SQL Server 2008 或更早），可以使用自定义函数来实现拆分。

示例

创建一个拆分字符串的用户定义函数 SplitString：

CREATE FUNCTION dbo.SplitString (@InputString NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @Start INT, @End INT;
    SET @Start = 1;
    SET @End = CHARINDEX(@Delimiter, @InputString);

    WHILE (@Start <= LEN(@InputString))
    BEGIN
        IF (@End = 0)
            SET @End = LEN(@InputString) + 1;

        INSERT INTO @Result (Value)
        VALUES (LTRIM(RTRIM(SUBSTRING(@InputString, @Start, @End - @Start))));

        SET @Start = @End + 1;
        SET @End = CHARINDEX(@Delimiter, @InputString, @Start);
        IF (@End = 0 AND @Start > LEN(@InputString))
            BREAK;
    END

    RETURN;
END;

使用该函数：

CREATE TABLE T (Id INT, Values VARCHAR(255));
INSERT INTO T (Id, Values) VALUES (1, 'A,B,C,D');

SELECT T.Id, dbo.SplitString(T.Values, ',').Value
FROM T
CROSS APPLY dbo.SplitString(T.Values, ',');

3. 使用临时表或表变量

        如果你有一组固定的字符串需要拆分，可以使用临时表或表变量来实现。

示例

使用临时表：

CREATE TABLE #TempTable (Id INT, Values VARCHAR(255));
INSERT INTO #TempTable (Id, Values) VALUES (1, 'A,B,C,D');

WITH SplitValues AS (
    SELECT Id, Value,
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM #TempTable
    CROSS APPLY STRING_SPLIT(#TempTable.Values, ',')
)
SELECT Id, Value
FROM SplitValues;

DROP TABLE #TempTable;

4. 使用自定义拆分表

创建一个拆分表来存储拆分后的结果。

示例

创建拆分表：

CREATE TABLE SplitTable (
    Id INT,
    Value NVARCHAR(MAX)
);

填充拆分表：

WITH SplitCTE AS (
    SELECT Id, Value,
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM T
    CROSS APPLY STRING_SPLIT(T.Values, ',')
)
INSERT INTO SplitTable (Id, Value)
SELECT Id, Value
FROM SplitCTE;

总结

        以上是一些常用的将单行数据转换为多行数据的方法。具体选择哪种方法取决于你的 SQL Server 版本和个人偏好。如果你使用的是 SQL Server 2016 或更高版本，推荐使用 STRING_SPLIT 函数，因为它简单且易于维护。对于早期版本，可以使用自定义函数或临时表/表变量来实现相同的功能。

相关文章