将多个逗号分隔的列拆分为行 [英] Split multiple comma separated columns into rows

问题描述

我有一个表(SQL Server)，它在多列中有逗号分隔的值，如下所示:

I have one table (SQL Server), which has comma separated values in multiple columns, like below:

Rule_ID    ListType_ID    Values
1          1,2            100,200
2          3,4            300,400

我想拆分逗号分隔的值并将它们转换为行.

I want to split the comma separated values and convert them into rows.

所需的输出必须如下所示:

The required output must be like below:

Rule_ID    ListType_ID    Values
1          1              100
1          2              200
2          3              300
2          4              400

我尝试了以下查询:

DECLARE @TEMP AS TABLE (
    [Rule_ID] INT,
    [ListType_ID] VARCHAR(MAX),
    [Values] VARCHAR(MAX)
)

INSERT INTO @TEMP
SELECT 1, '1,2', '100,200'
UNION ALL
SELECT 2, '3,4', '300,400' 

SELECT 
    [Rule_ID],
    PARSENAME(REPLACE(Split1.b.value('.', 'VARCHAR(100)'),'-','.'),1) AS [ListType_ID],
    PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) AS [Values] 
FROM  
(
    SELECT [Rule_ID],
    CAST ('<M>' + REPLACE([ListType_ID], ',', '</M><M>') + '</M>' AS XML) AS [ListType_ID],
    CAST ('<M>' + REPLACE([Values], ',', '</M><M>') + '</M>' AS XML) AS [Values] 
    FROM @TEMP     
) AS A 
CROSS APPLY [Values].nodes ('/M') AS Split(a)
CROSS APPLY [ListType_ID].nodes ('/M') AS Split1(b)
ORDER BY [Rule_ID], [ListType_ID], [Values]

此查询返回以下输出，与所需的输出不同:

This query returns the below output, which is different from the required output:

Rule_ID    ListType_ID    Values
1          1              100
1          1              200
1          2              100
1          2              200
2          3              300
2          3              400
2          4              300
2          4              400

请在这里帮助我....!!!!

Please help me here....!!!!

推荐答案

请检查以下SQL脚本

为了在 SQL 中拆分字符串，我使用了以下用户定义的其中之一 SQL 拆分字符串函数

To split string in SQL I used one of the following user-defined SQL split string functions

这些函数返回我在 WHERE 子句中使用的拆分字符串的顺序，以便我可以一对一地映射字段值

These functions return the order of the splitted string which I used in WHERE clause so I can map field values one-to-one

/*
create table Table_1 (
    Rule_ID int,    ListType_ID    varchar(max), [Values] varchar(max)
)
insert into Table_1 select 1,'1,2','100,200'
insert into Table_1 select 2,'3,4','300,400'
*/
select 
Rule_ID,
idlist.val as ListType_ID,
valueslist.val as [Values]
from Table_1
cross apply dbo.SPLIT(ListType_ID,',') as idlist
cross apply dbo.SPLIT([Values],',') as valueslist
where 
idlist.id = valueslist.id

这篇关于将多个逗号分隔的列拆分为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！