SQL Server以逗号拆分多列|极客教程

SQL Server以逗号拆分多列

在数据库中，有时候我们会遇到一列数据包含多个值，并且这些值是以逗号分隔的。这种情况下，我们可能需要将这一列数据拆分成多列，以便更好地进行数据分析和处理。本文将详细介绍在SQL Server中如何实现以逗号拆分多列的操作。

方法一：使用XML函数

SQL Server提供了一个内置的函数STRING_SPLIT()用于将字符串按照指定的分隔符进行拆分。我们可以利用这个函数将包含多个值的列拆分成多条记录，然后使用PIVOT函数将这些记录转换成多列数据。

假设我们有一个名为Products的表，其中包含一个列ProductList，存储着多个产品名称，并且这些产品名称是以逗号分隔的。我们可以按照以下步骤进行拆分：

-- 创建示例表
CREATE TABLE Products (
    ProductList VARCHAR(MAX)
);

-- 插入示例数据
INSERT INTO Products (ProductList)
VALUES ('Apple, Banana, Orange'),
       ('Carrot, Broccoli, Tomato');

-- 使用XML函数进行拆分
SELECT ProductList,
       LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)')) ) AS Product
FROM (
    SELECT ProductList,
           CAST('<XMLRoot><RowData>' + REPLACE(ProductList, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
    FROM Products
) t
CROSS APPLY x.nodes('/XMLRoot/RowData') m(n);

在上面的代码中，我们首先创建了一个名为Products的表，并插入了一些示例数据。然后我们使用XML函数将ProductList列按照逗号进行拆分，并将拆分后的数据展开为多个行。最后通过SELECT语句将拆分后的数据查询出来。

运行以上代码后，我们将得到如下结果：

ProductList	Product
Apple, Banana, Orange	Apple
Apple, Banana, Orange	Banana
Apple, Banana, Orange	Orange
Carrot, Broccoli, Tomato	Carrot
Carrot, Broccoli, Tomato	Broccoli
Carrot, Broccoli, Tomato	Tomato

接下来，我们可以通过使用PIVOT函数将上面的查询结果转换为多列数据，具体操作如下：

-- 使用PIVOT函数将结果转换为多列数据
SELECT ProductList,
       [1] AS Product1,
       [2] AS Product2,
       [3] AS Product3
FROM (
    SELECT ProductList,
           LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)')) ) AS Product,
           ROW_NUMBER() OVER (PARTITION BY ProductList ORDER BY (SELECT NULL)) AS rn
    FROM (
        SELECT ProductList,
               CAST('<XMLRoot><RowData>' + REPLACE(ProductList, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM Products
    ) t
    CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
) x
PIVOT (
    MAX(Product) FOR rn IN ([1], [2], [3])
) p;

运行以上代码后，我们将得到如下结果：

ProductList	Product1	Product2	Product3
Apple, Banana, Orange	Apple	Banana	Orange
Carrot, Broccoli, Tomato	Carrot	Broccoli	Tomato

方法二：使用自定义函数

除了使用XML函数之外，我们还可以通过创建自定义函数来实现以逗号拆分多列的操作。下面是一个示例代码，演示了如何创建一个自定义函数并使用它将包含多个值的列拆分成多列：

-- 创建自定义函数
CREATE FUNCTION dbo.fn_SplitString(
    @String VARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @ResultTable TABLE (Value VARCHAR(MAX))
AS
BEGIN
    DECLARE @DelimiterPosition INT;
    DECLARE @Value VARCHAR(MAX);

    WHILE CHARINDEX(@Delimiter, @String) > 0
    BEGIN
        SET @DelimiterPosition = CHARINDEX(@Delimiter, @String);
        SET @Value = SUBSTRING(@String, 1, @DelimiterPosition - 1);
        INSERT INTO @ResultTable VALUES (@Value)
        SET @String = SUBSTRING(@String, @DelimiterPosition + 1, LEN(@String));
    END
    INSERT INTO @ResultTable VALUES (@String);

    RETURN;
END;

-- 使用自定义函数进行拆分
SELECT ProductList,
       [1] AS Product1,
       [2] AS Product2,
       [3] AS Product3
FROM (
    SELECT ProductList,
           Value,
           ROW_NUMBER() OVER (PARTITION BY ProductList ORDER BY (SELECT NULL)) AS rn
    FROM Products
    CROSS APPLY dbo.fn_SplitString(ProductList, ',')
) x
PIVOT (
    MAX(Value) FOR rn IN ([1], [2], [3])
) p;