0

I have a table where it contains data in below format enter image description here

How to achieve this in MS SQL Server.

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
Manoharan
  • 49
  • 1
  • 8
  • Which version of **`MS SQL Server`** are you using? – Ullas Sep 03 '18 at 09:48
  • 1
    Possible duplicate https://stackoverflow.com/questions/26293157/splitting-comma-separated-values-in-columns-to-multiple-rows-in-sql-server – Denis Rubashkin Sep 03 '18 at 09:50
  • 4
    Possible duplicate of [Splitting Comma separated values in columns to multiple rows in Sql Server](https://stackoverflow.com/questions/26293157/splitting-comma-separated-values-in-columns-to-multiple-rows-in-sql-server) – DavidG Sep 03 '18 at 09:51
  • 1
    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 Sep 03 '18 at 09: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 Sep 03 '18 at 09:54
  • @t-clausen.dk Yes it is a duplicate, the OP just needs to apply some thinking to make it specific to their situation. – DavidG Sep 03 '18 at 09:55
  • @DavidG for this you will need xmlsplit or a homebuild split function that returns the number of the value as well, for the other question you can simply use string_split – t-clausen.dk Sep 03 '18 at 09:56
  • @t-clausen.dk What? There is no xmlsplit? Also, no you just use normal string split, do it twice and join the rows together with a DISTINCT. – DavidG Sep 03 '18 at 09:58
  • @DavidG [XML split](https://stackoverflow.com/questions/29771283/how-to-write-sql-query-for-this-result/29771520#29771520) and your solution is not possible, it return 1+4+9 rows instead of the expected 1+2+3 rows. Check the answer from Larmu to comprehend this better – t-clausen.dk Sep 03 '18 at 10:08
  • @Wanderer, I am using Azure SQL Server, compatibility_level is 130, but cant use string split function. – Manoharan Sep 03 '18 at 10:11
  • @RamBhaskar do you mean the built in one, or the one in my answer? – Thom A Sep 03 '18 at 10:26
  • @Larnu, I have not tried with DelimitedSplit8K, Let me check that. – Manoharan Sep 03 '18 at 10:33
  • I've also added a `OPENJSON` solution for you (should you have more than 8000 characters in a single delimited list), however, you should still be fixing your data model. – Thom A Sep 03 '18 at 10:39

1 Answers1

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];
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • 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. – Manoharan Sep 12 '18 at 04:46
  • @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 `,`? – Thom A Sep 12 '18 at 07:11
  • 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. – Manoharan Sep 21 '18 at 08:11
  • @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. – Thom A Sep 21 '18 at 08:20