SQL Server:将分隔为多个列的单列值逗号拆分

Question

现在我是SQL Server的新手，并尝试拆分一个用逗号分隔的列值，我希望每个数据都有一个单独的列。

2270,2290,2234,2245,2256

我希望结果是这样的：

| Split column |
2270
2290
2234

任何想法将不胜感激，因为我在这里太深入了？我已经String_split并将兼容性设置为140，因为我正在使用SQL Server 2017。但我得到的只是

2270,2290,2234
2270,2290,2234
2270,2290,2234

这是查询，其中输出将看起来像我在问题中发布的方式，需要拆分：

SELECT ERSBusinessLogic_InputDataSeries 
FROM [AnimalProductsCoSD].[CoSD].[ERSBusinessLogic]
WHERE
ERSBusinessLogic_InputGeographyDimensionID = 7493
AND ERSBusinessLogic_InputTimeDimensionValue = 'all months'
AND ERSBusinessLogic_Type = 'HS10 aggregation'; 

Answer 1

您遇到了问题，因为split_string()不保持结果的顺序。 嘎嘎！ 这已经是对Microsoft的功能请求。

您的字符串在其格式中看起来非常规范。 如果是这样，您可以使用substring()：

SELECT LEFT(ERSBusinessLogic_InputDataSeries, 4) as str1,
SUBSTRING(ERSBusinessLogic_InputDataSeries, 5, 4) as str2,
RIGHT(ERSBusinessLogic_InputDataSeries, 4) as str3
FROM [AnimalProductsCoSD].[CoSD].[ERSBusinessLogic]
WHERE ERSBusinessLogic_InputGeographyDimensionID = 7493 AND
ERSBusinessLogic_InputTimeDimensionValue = 'all months' AND
ERSBusinessLogic_Type = 'HS10 aggregation'; 

如果排序无关紧要，您可以使用split_string()：

SELECT ss.*
FROM [AnimalProductsCoSD].[CoSD].[ERSBusinessLogic] ebl OUTER APPLY
(SELECT MAX(CASE WHEN seqnum = 1 THEN val END) as str1,
MAX(CASE WHEN seqnum = 2 THEN val END) as str2
MAX(CASE WHEN seqnum = 3 THEN val END) as str3
FROM (SELECT ss.val, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as seqnum
FROM split_string(ERSBusinessLogic_InputDataSeries, ',') ss(val)
) ss
) ss
WHERE ERSBusinessLogic_InputGeographyDimensionID = 7493 AND
ERSBusinessLogic_InputTimeDimensionValue = 'all months' AND
ERSBusinessLogic_Type = 'HS10 aggregation';