字符串按照逗号分隔成数据集YearValue-CSDN博客

本文链接：https://blog.csdn.net/weixin_45042852/article/details/133948725

将表Ctest的Rangeyear字段按照逗号分隔成数据集YearValue

ID	PEID	Rangeyear						startDate			endDate			value
0	100001	2020,2021,2022						2020-09-01 00:00:00.000	2023-08-08 00:00:00.000	0

变成
ID	PEID	Rangeyear		YearValue			startDate			endDate			value
0	100001	2020,2021,2022		2020				2020-09-01 00:00:00.000	2023-08-08 00:00:00.000	0
0	100001	2020,2021,2022		2021				2020-09-01 00:00:00.000	2023-08-08 00:00:00.000	0
0	100001	2020,2021,2022		2022				2020-09-01 00:00:00.000	2023-08-08 00:00:00.000	0

-- 选择需要的列，同时将Rangeyear字段拆分成多行数据
SELECT
    ID,
    PEID,
    Rangeyear,
    Year.value('.', 'INT') AS YearValue,  -- 将逗号分隔值转换为整数
    startDate,
    endDate,
    value
FROM Ctest
CROSS APPLY (
    -- 第一个CROSS APPLY子查询：将逗号分隔值转换为XML格式
    SELECT CAST('<M>' + REPLACE(Rangeyear, ',', '</M><M>') + '</M>' AS XML) AS YearXml
) AS YearNodes
CROSS APPLY YearNodes.YearXml.nodes('/M') AS Year(Year);
--第二个 CROSS APPLY 子查询：使用 XML 节点查询从 XML 数据中提取 YearValue（分隔的年份）。

那什么是CROSS APPLY 和 XML 转换？

他们分别用于在查询中处理和转换数据。

1.CROSS APPLY:

CROSS APPLY 是 SQL Server 中的一种表达式，通常与表值函数一起使用。它用于在查询中执行表值函数，并将其结果集与查询的主要结果集进行关联。
CROSS APPLY 常用于将表值函数应用于每一行主查询的数据，以便动态生成数据集或执行某些操作。这对于拆分、转换或展开数据非常有用。
与 INNER JOIN 或 LEFT JOIN 不同，CROSS APPLY 不是将两个表连接，而是将主查询的结果集与表值函数的结果集关联。这允许对每行数据进行自定义处理。

SELECT Orders.OrderID, ProductName
FROM Orders
CROSS APPLY GetProducts(Orders.OrderID) AS Products;

2.XML 转换:

XML 转换是将文本数据转换为 XML 数据类型的过程。在 SQL Server 中，可以使用 CAST 或 CONVERT 函数将文本转换为 XML。
XML 数据类型在 SQL Server 中允许存储和查询 XML 数据，以及使用 XPath 表达式检索和处理 XML 数据。
XML 转换通常用于处理包含结构化数据的文本，如从外部源导入的 XML 数据或包含逗号分隔值的字符串。

-- 转换文本为 XML
DECLARE @xmlData XML = '<person><name>John</name><age>30</age></person>';

-- 使用 XPath 表达式查询 XML 数据
SELECT @xmlData.value('(/person/name)[1]', 'NVARCHAR(50)') AS Name,
       @xmlData.value('(/person/age)[1]', 'INT') AS Age;