How to split string in multiple columns in SQL Server 2012?

Question

I have a table in with a column that has values like this:

["L", "L", "L", "D", "L", "D", "L", "L", "L", "D"] ,

I have to add it in another table which will be 10 columns and response will be 1 for character "L" , 0 for character "D".

My column names will be dynamic as the string contains 240 items of L and D. In the above example, only 10 are present. Column name will be item1, item2 and so on.

Firstly, why do you need to do this? What are the different columns representing and why is this data not being recorded as rows? — iamdave
– iamdave, Commented Nov 13, 2017 at 10:07
@iamdave - Client want to the data in that format to analysis . the columns representing the boolean value 1 for L and 0 for D . The one string recorded as one row, I want to split in columns with that column names like item1, item2 and so on. — KulOmkar
– KulOmkar, Commented Nov 13, 2017 at 10:10
There are numerous questions and answers on SO about string splitting. Have you tried anything? — HoneyBadger
– HoneyBadger, Commented Nov 13, 2017 at 10:11

Yogesh Sharma · Accepted Answer · 2017-11-13 10:38:31Z

1

What about XML node method have you tried ever

select distinct
      case (a.value ('/A[1]', 'VARCHAR(MAX)')) when '"L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column1],
      case (a.value ('/A[2]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column2],
      case (a.value ('/A[3]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column3],
      case (a.value ('/A[4]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column4],
      case (a.value ('/A[5]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column5],
      case (a.value ('/A[6]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column6],
      case (a.value ('/A[7]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column7],
      case (a.value ('/A[8]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column8],
      case (a.value ('/A[9]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column9],
      case (a.value ('/A[10]', 'VARCHAR(MAX)')) when ' "L"' THEN 1 WHEN ' "D"' THEN 0 END  [Column10]

      from 
(
    SELECT CAST('<A>'+REPLACE(REPLACE(REPLACE(@DATA, '[', ''), ']', ''), ',', '</A><A>')+'</A>' AS XML) AS Data 
    --FROM <table_name>
) A CROSS APPLY Data.nodes ('/A') as split(a)

Result :

Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10
    1       1       1       0       1       0       1        1      1       0

edited Nov 13, 2017 at 10:38

answered Nov 13, 2017 at 10:13

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

3 Comments

KulOmkar Over a year ago

yes , its helped me at initial stage , but now i have to convert that "L" to 1 and "D" to 0

Yogesh Sharma Over a year ago

use case or replace

Yogesh Sharma Over a year ago

@KulOmkar try edited solutions with case expression

Collectives™ on Stack Overflow

How to split string in multiple columns in SQL Server 2012?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related