5

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

I have tried the below query:

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....!!!!

Improve this question
5
  • 5
    There are 1,000's of answers on SO on how to split delimited strings; in fact I would go as far as to say it is the most common question related to SQL Server.. I would suggest having a search or a Google. There is also a inbuilt splitter in SQL Server, but that depends on the version you're using (you haven't mentioned or tagged your version, so I don't know if you can use it). Commented May 23, 2018 at 7:48
  • 2
    You may need one that provides ordinal position though, which they don't all do. Keep that in mind when searching, however, as a tip, I suggest "DelimitedSplit8k" (by Jeff Moden). Commented May 23, 2018 at 7:51
  • 2
    On a different note, I certainly wouldn't say the question was worth a downvote. Yes the OP hasn't shown due-diligence of performing a search, however, they've certainly tickedthe boxes of showing what they've tried, given sample data, expected output, etc. It's in a far better position than many other questions we see posted here. Commented May 23, 2018 at 7:54
  • @Larnu it is a well formatted and relatable question. I wouldn't give a downvote for that, merely there are enough questions like this and could have easily found by searching it. Maybe not with provided sample code and that may be the reason for asking it here. Before asking a question on SO, you even get a recommendation to look it up. Commented May 23, 2018 at 7:59
  • Even though it's not your usual csv split dupe, I'm sure there are dupes for this question. However, I don't the the mood nor the time to handle SO search, and the question is properly written - so IMHO not only it does not deserve a downvote, it actually deserve an upvote. Commented May 23, 2018 at 9:19

2 Answers 2

Reset to default
5

Please check following SQL script

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

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Though I would recommend using Jeff Moden's DelimitedSplit8K for the split rather then a recursive cte, this answer is good, so +1.
I guess using a SQL Server CLR split function kodyaz.com/articles/… is also performing good. An additional frequently used string split method is using XML methods kodyaz.com/articles/…
Here is another good link about splitting strings performance.
2

Using CTE, a double CROSS APPLY and XML based split you can use this script:

;WITH Splitted AS 
( 
    SELECT    
        [Rule_ID]
        ,CAST('<x>' + REPLACE([ListType_ID],',','</x><x>') + '</x>' AS XML) AS  [ListType_ID_Val]    
        ,CAST('<x>' + REPLACE([Values],',','</x><x>') + '</x>' AS XML) AS  [Values_Val]    
    FROM @TEMP 
) 
SELECT     
    Rule_ID, cs.VAL as[ListType_ID], cd.VAL as [Values]
FROM Splitted
    CROSS APPLY (VALUES ('a',ListType_ID_Val.value(N'/x[1]','int') ),
      ('b',ListType_ID_Val.value(N'/x[2]','int') )
    )CS (COL,VAL) 
     CROSS APPLY (VALUES ('a',Values_Val.value(N'/x[1]','int') ),
      ('b',Values_Val.value(N'/x[2]','int') )
    )CD (COL,VAL) 
where CS.COL = CD.COL

Results:

enter image description here

Improve this answer

1 Comment

I doubt, that there are always exactly two values per each group. Therefore it's not a good idea to call XML's .value() with fixed positions. This can be done with .nodes and a JOIN.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.