Split CSV values as columns

To split a CSV value in a given column into different individual columns use the following trick. 

Firstly, let us look at the different functions that are involved in this process. 

1. Table Value Constructor: The Value clause can be used to create a table out of values. This clause is used in the insert statement however its interesting to note that it can also be used in joins to create a derived table with fixed values. 

E.g.: In the examples below a derived table Source has been created using literal values. 

select Source.NewReasonType, Source.NewName
from (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion')) 
     AS Source (NewName, NewReasonType)

2. Apply operator: Just like the join operator we have another operator apply which has been introduced in SQL and this operator indicates to the SQL Engine that the clause has to be evaluated for each row

3. JSON_VALUE: This function is used to extract a scalar value from a Json string. The function accepts 2 paramters 1. Json expression and 2. subscript of the node to refer.

E.g.

       

DECLARE @data NVARCHAR(MAX);
SET @data = N'{  
     "info":{    
       "ID":1,  
     "Name":"Akshat",
       "address":{    
         "City":"Gurgaon",  
         "Country":"India"  
       },  
       "Favorite_Subject":["English", "Science"]  
    },  
    "type":"student"  
 }';  
SELECT JSON_VALUE(@data, '$.info.Name') AS 'Name', 
       JSON_VALUE(@data, '$.info.Favorite_Subject[1]') AS 'Favorite Subject';

 

E.g: If Display value contains the dimension values separated by a pipe (|) the below statement will break it into individual dimension columns

 select
JSON_VALUE(JS,'$[0]') Dim1, JSON_VALUE(JS,'$[1]') Dim2, JSON_VALUE(JS,'$[2]') Dim3, JSON_VALUE(JS,'$[3]') Dim4
from DIMENSIONSETENTITY T17
Cross Apply (values ('["' + replace( T17.DISPLAYVALUE ,'|','","')+'"]') ) B(JS)