How to split a column into multiple columns using SQL

Posted on by admin

I have a column that has both the name of the column and the value for that column in the following format:

column_name
name1: value1 name2: valu2 name3: value3 name4: value4 name5: value5

Instead of having the setting above I want to have the columns in the following format:

name1 name2 name3 name4 name5
value1 value2 value3 value4 value5

The issue is that those values are not consistent through the entire column, sometimes I will have a combination of those values, sometimes none, and sometimes just one, but it doesn’t matter how the order is, all those columns will have to be created. how can call them in a generic way that I don’t have to specify the values like this:

CASE WHEN CHARINDEX(' ',column_name)>0 
         then SUBSTRING(column_name,1,CHARINDEX(' ',column_name)-1) 
         else column_name end name1, 
    CASE WHEN CHARINDEX(' ',column_name) > 0 
         THEN SUBSTRING(column_name,CHARINDEX(' ',column_name)+1,len(column_name))  
         ELSE NULL END as name2

Related Posts