Comma separated values in to multiple rows in SQL Server

Question

I have a table where it contains data in below format

How to achieve this in MS SQL Server.

Possible duplicate of Splitting Comma separated values in columns to multiple rows in Sql Server — DavidG
– DavidG, Commented Sep 3, 2018 at 9:51
What were wrong with the (probably 100's) of other questions with answers that were presented to you when you searched and wrote this question? — Thom A
– Thom A ♦, Commented Sep 3, 2018 at 9:52
This is not a duplicate, this is a different question with multiple columns with multiple values, using a bad data model — t-clausen.dk
– t-clausen.dk, Commented Sep 3, 2018 at 9:54

Thom A · Accepted Answer · 2018-09-03 10:53:14Z

1

This uses DelimitedSplit8K, as information on the ordinal position is required (something STRING_SPLIT and many other splitters don't supply). The below is Pseudo SQL as well, as the OP has provided images, rather that textual data:

SELECT {YourColumns}
FROM YourTable YT
     CROSS APPLY dbo.DelimitedSplit8K(YT.Qualification,',') DSq
     CROSS APPLY dbo.DelimitedSplit8K(YT.Instituion,',') DSi
WHERE DSq.ItemNumber = DSi.ItemNumber;

The true answer here, as has been mentioned in the comments, however, is to fix the data model.

An alternative method would be to use OPENJSON. This is something I have only been introduced to recently, and I don't have access to a SQL Server 2016 instance to test this against (I have used SQL Fiddle to test it runs though, but not against the image provided for my same reason above). I beleive this should also achieve your goal though:

SELECT OJq.[value], OJi.[Value]
FROM YourTable YT
     CROSS APPLY (SELECT ca.[Key], ca.[value]
                  FROM OPENJSON('["' + REPLACE(YT.Qualification,',','","') + '"]') ca) OJq
     CROSS APPLY (SELECT ca.[Key], ca.[value]
                  FROM OPENJSON('["' + REPLACE(YT.Instituion,',','","') + '"]') ca) OJi
WHERE OJq.[Key] = OJi.[Key];

edited Sep 3, 2018 at 10:53

answered Sep 3, 2018 at 10:01

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Manoharan Over a year ago

I want to replace comma with semi colon. I have replaced comma with semi colon, but it throws error.:JSON text is not properly formatted. Unexpected character ';' is found at position 42."Do you have any idea about it.

Thom A Over a year ago

@RamBhaskar what do you mean? There are no commas in the final dataset. Do you mean on your original data it is now separated by a ; instead of a ,?

Manoharan Over a year ago

Yes, Now delimiter is semicolon; I used the query that you had given but in the same statement, I replaced comma with semi colon using replace function.

Thom A Over a year ago

@RamBhaskar If the delimiter is now a ; then just change the REPLACE from a , to ;. An important part of using the SQL you get from the volunteers here is understanding it; if you don't you shouldn't be using it. Changing the delimiter is the simplest thing you can do here.

Collectives™ on Stack Overflow

Comma separated values in to multiple rows in SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related