Split comma separated string in a column / string to rows and convert into columns in SQL Server

This article is about how to split comma separated string value in a column or string variable value into multiple columns in SQL Server. Here delimiter can be any special character such as comma (,), hash (#), dollar ($), etc.,.

1. Split comma separated string value and convert into columns
2. Split comma separated string in a column and convert into columns

Split comma separated string value and convert into columns
For example, if you have the following string and if you want each of the value comma separated string into a separate column.

-- Input variable (comma seperated)
declare @strval varchar(45)='mahesh,satish,krishna,lakshmi,rakesh'; 
-- Drop temp table if exists
IF OBJECT_ID('tempdb..##strspilt') IS NOT NULL DROP TABLE ##strspilt;
-- Identifying the start and end positions of comma separated values (CTE, CHARINDEX)
-- spilt the string into rows using start and end position
-- final resultset will be copied into temp table (##strspilt)
;with str_spilt_col AS ( 
     SELECT 1 AS start_pos,
        CHARINDEX(',',@strval) AS end_pos,1 AS id
    UNION all
    SELECT CHARINDEX(',',@strval,end_pos)+1 AS start_pos,
        CASE
        WHEN CHARINDEX(',',@strval,end_pos+2)=0 THEN
        DATALENGTH(@strval)+1
        ELSE CHARINDEX(',',@strval,end_pos+2)
        END AS end_pos, id+1 AS id
    FROM str_spilt_col
    WHERE end_pos<=DATALENGTH(@strval)-charindex(',',REVERSE(@strval))+1 )
SELECT SUBSTRING(@strval,
        start_pos,
        end_pos-start_pos) AS msg,
        id into ##strspilt
FROM str_spilt_col 
-- convert rows to columns (DYNAMIC SQL)
-- Prepare dynamic query  
declare @sql nvarchar(500)='',@i int=1 
while (@i<= (SELECT max(id) FROM ##strspilt)) 
begin 
    if (@sql='') 
        SET @sql+='(select msg from ##strspilt where id='+cast(@i AS nvarchar)+') as col'+cast(@i AS nvarchar)
    ELSE 
        SET @sql+=',(select msg as col'+cast(@i AS nvarchar)+' from ##strspilt where id='+cast(@i AS nvarchar)+') as col'+cast(@i AS nvarchar) 
    SET @i+=1 
end; 
SET @sql='select distinct '+@sql 
execute (@sql)

Split comma separated string in a column and convert into columns
For example, if you have the following column (Name_age_gender_height_weight) and if you want each of the value comma separated string from that column into a separate column.

declare @strspilt table
(
  Name_age_gender_height_weight varchar(25)
)
insert into @strspilt values
('mahesh,31,M,178,75'),
('suresh,35,M,172,85'),
('sita,25,F,168,58')

select p1.Pos,p2.Pos,p3.Pos,p4.Pos,
substring(Name_age_gender_height_weight,1, P1.Pos - 1) as Name,
substring(Name_age_gender_height_weight, P1.Pos + 1, P2.Pos - P1.Pos - 1) as Age,
substring(Name_age_gender_height_weight, P2.Pos + 1, P3.Pos - P2.Pos - 1) as Gender,
substring(Name_age_gender_height_weight, P3.Pos + 1, P4.Pos - P3.Pos - 1) as Height,
substring(Name_age_gender_height_weight, P4.Pos + 1, 10) as [weight]
from @strspilt
  cross apply (select (charindex(',', Name_age_gender_height_weight))) as P1(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P2.Pos+1))) as P3(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P3.Pos+1))) as P4(Pos)

That’s it.Let me know if you have any questions, suggestions or comments.