sql - Convert unknown number of comma separated varchars within 1 column into multiple columns

Question

Let me say upfront that I'm a brand-spanking-new SQL Developer. I've researched this and haven't been able to find the answer.

I'm working in SSMS 2012 and I have a one-column table (axis1) with values like this:

axis1
296.90, 309.4
296.32, 309.81
296.90
300.11, 309.81, 311, 313.89, 314.00, 314.01, V61.8, V62.3

I need to convert this column into multiple columns like so:

axis1    axis2    axis3  axis4
296.90   309.4    null   null
296.32   309.81   null   null
296.90   null     null   null
300.11   309.81   311    313.89...    

So far I've tried/considered:

select case when charindex(',',Axis1,1)>0 
            then substring(Axis1,1,CHARINDEX(',',Axis1,1)-1)
            else Axis1
       end as Axis1
from tablex

That works fine for a known number of column values, but there could be 0, 1, or 20+ values in this column.

Is there any way to split an unknown quantity of comma-separated values that are in one column into multiple single-value columns? Thanks in advance for any help everyone!

Answer 1

I made one assumption while creating this answer, which is that you need this as a separate stored proc.

Step 1

Create a data type to enable the use of passing a table-valued parameter (TVP) into a stored proc.

use db_name
GO
create type axisTable as table 
    (
        axis1 varchar(max)
    )
GO

Step 2

Create the procedure to parse out the values.

USE [db_name]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_util_parse_out_axis] 
    (
        @axis_tbl_prelim axisTable readonly
    )
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @axis_tbl axisTable

    --since TVP's are readonly, moving the data in the TVP to a local variable
    --so that the update statement later on will work as expected
    insert into @axis_tbl
    select *
    from @axis_tbl_prelim

    declare @comma_cnt int
        , @i int 
        , @sql_dyn nvarchar(max)
        , @col_list nvarchar(max)

    --dropping the global temp table if it already exists
    if object_id('tempdb..##axis_unpvt') is not null
        drop table ##axis_unpvt

    create table ##axis_unpvt
        (
            axis_nbr varchar(25)
            , row_num int
            , axis_val varchar(max)
        )

    --getting the most commas
    set @comma_cnt = (select max(len(a.axis1) - len(replace(a.axis1, ',', '')))
                        from @axis_tbl as a)

    set @i = 1
    while @i <= @comma_cnt + 1
    begin --while loop

        --insert the data into the "unpivot" table one parsed value at a time (all rows) 
        insert into ##axis_unpvt
        select 'axis' +  cast(@i as varchar(3))
        , row_number() over (order by (select 100)) as row_num --making sure the data stays in the right row
        , case when charindex(',', a.axis1, 0) = 0 and len(a.axis1) = 0 then NULL
                when charindex(',', a.axis1, 0) = 0 and len(a.axis1) > 0 then a.axis1
                when charindex(',', a.axis1, 0) > 0 then replace(left(a.axis1, charindex(',', a.axis1, 0)), ',', '')
                else NULL
        end as axis1
        from @axis_tbl as a

        --getting rid of the value that was just inserted from the source table
        update a
        set a.axis1 = case when charindex(',', a.axis1, 0) = 0 and len(a.axis1) > 0 then NULL
                           when charindex(',', a.axis1, 0) > 0 then rtrim(ltrim(right(a.axis1, (len(a.axis1) - charindex(',', a.axis1, 0)))))
                           else NULL
                      end
        from @axis_tbl as a
        where 1=1
        and (charindex(',', a.axis1, 0) = 0 and len(a.axis1) > 0
             or charindex(',', a.axis1, 0) > 0) 

        --incrementing toward terminating condition
        set @i += 1

    end --while loop

    --getting list of what the columns will be after pivoting
    set @col_list = (select stuff((select distinct ', ' + axis_nbr
                            from ##axis_unpvt as a
                            for xml path ('')),1,1,''))

    --building the pivot statement
    set @sql_dyn = '
    select '
    + @col_list + 
    '
    from ##axis_unpvt as a
    pivot (max(a.axis_val)
            for a.axis_nbr in ('
                                + @col_list + 
                                ')) as p'

    --executing the pivot statement
    exec(@sql_dyn);

END

Step 3

Make a procedure call using the data type created in Step 1 as the parameter.

use db_name
go

declare @tvp as axisTable

insert into @tvp values ('296.90, 309.4')
insert into @tvp values ('296.32, 309.81')
insert into @tvp values ('296.90')
insert into @tvp values ('300.11, 309.81, 311, 313.89, 314.00, 314.01, V61.8, V62.3')

exec db_name.dbo.usp_util_parse_out_axis @tvp

Results from your example are as follows: