如何在SQL Server中将employee值行分隔为列

SELECT value AS employee
FROM STRING_SPLIT('John,Doe,Jane,Smith', ',');

DECLARE @xml XML;
SET @xml = '<employees><employee>John</employee><employee>Doe</employee><employee>Jane</employee><employee>Smith</employee></employees>';

SELECT
    employee.value('.', 'VARCHAR(100)') AS employee
FROM
    @xml.nodes('/employees/employee') AS employees(employee);

DECLARE @employees VARCHAR(MAX) = 'John,Doe,Jane,Smith';

WITH cte AS (
    SELECT
        LEFT(@employees, CHARINDEX(',', @employees) - 1) AS employee,
        RIGHT(@employees, LEN(@employees) - CHARINDEX(',', @employees)) AS remaining
    UNION ALL
    SELECT
        LEFT(remaining, CHARINDEX(',', remaining) - 1),
        RIGHT(remaining, LEN(remaining) - CHARINDEX(',', remaining))
    FROM
        cte
    WHERE
        CHARINDEX(',', remaining) > 0
    UNION ALL
    SELECT
        remaining,
        ''
    FROM
        cte
    WHERE
        CHARINDEX(',', remaining) = 0
)
SELECT employee
FROM cte;