sql - 基于一列中的字符串将一行转换为多行多列

Question

我必须根据一列中字符串中的数值将一行转换为多行

示例输入：

EmpId | work date  | String 
------+------------+--------------------------------------------------------
 1234 | 12/10/2020 | The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters

示例输出：

Empid | Work Date  | Clock | Radius 
------+------------+-------+--------
1234  | 12/10/2020 | 12:03 | 209759
1234  | 12/10/2020 | 12:04 | 209758

根据字符串中的数字，可以有 n 个值，它必须分成两列和两行。

请帮我解决这个问题 - 谢谢

标签： sqlsql-server

Answer 1

请尝试以下解决方案。

它很乱，但工作：

1. 首先 CTE通过 XML 和 XQuery对 free_text列进行标记，并过滤掉不带逗号的标记。
2. 第二个 CTE 正在从 XML 中获取时钟列。
3. 第三个 CTE 正在从 XML 中获取Radius列。
4. Final SELECT 将这一切结合在一起。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (emp_id INT, work_date DATE, free_text NVARCHAR(MAX))
INSERT INTO @tbl (emp_id, work_date, free_text) VALUES
(1234, '12/10/2020',N'The following clocks 12:03,12:04 are outside of the allowed radius by 209759,209758 meters');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1)
    , @comma CHAR(1) = ',';

WITH rs AS
(
    SELECT emp_id, work_date
        , CAST('<root><r><![CDATA[' + 
            REPLACE(free_text COLLATE Czech_BIN2, @separator, ']]></r><r><![CDATA[') + ']]></r></root>' AS XML)
        .query('
        for $x in /root/r
        where contains($x, sql:variable("@comma"))
        return $x
        ') AS result
    FROM @tbl
), clock AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
        , rs.*
        , z.value AS clock
    FROM rs
        CROSS APPLY result.nodes('/r[1]') AS t(c)
        CROSS APPLY STRING_SPLIT(c.value('.', 'VARCHAR(20)'), @comma) AS z
), Radius AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
        , rs.*
        , z.value AS Radius
    FROM rs
        CROSS APPLY result.nodes('/r[2]') AS t(c)
        CROSS APPLY STRING_SPLIT(c.value('.', 'VARCHAR(20)'), @comma) AS z
)
SELECT c.emp_id, c.work_date, c.clock, r.Radius
FROM clock AS c
    INNER JOIN Radius AS r ON r.seq = c.seq
        AND r.emp_id = c.emp_id;

输出

+--------+------------+-------+--------+
| emp_id | work_date  | clock | Radius |
+--------+------------+-------+--------+
|   1234 | 2020-12-10 | 12:03 | 209759 |
|   1234 | 2020-12-10 | 12:04 | 209758 |
+--------+------------+-------+--------+