Table from comma separated Column at two level

Question

I have one old db in which there are two columns which contain comma separated values like this,

Table Schema with data

Now my problem is that I am trying to import those values into another database which is normalized. So instead of comma separated values, I need to convert those values into a tabular format .

So my output should be look like this,

enter image description here

score 0 · Answer 1 · answered Mar 21 '14 at 12:39

You need to define what those columns mean. In your example are you discarding the original ID column, in which case what does "1,2,3" & "A,B" actually mean?

I'd probably approach this by cursoring through each row and using a split function to convert each field to a table of values.

create FUNCTION dbo.fn_Split1 (@sep nchar(1), @s nvarchar(4000))
RETURNS table
/**************************************************************************************************
 * Author:              http://stackoverflow.com/questions/314824/
 * Description:         splits a string into a table of values, with single-char delimiter.
 * Example Usage:

        select * from dbo.fn_split1(',', '1,2,5,2,,dggsfdsg,456,df,1,2,5,2,,dggsfdsg,456,df,1,2,5,2,,')
 **************************************************************************************************/
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
    FROM Pieces
  )

your answer is no where near to my requirement or expected result. may be its my mistake that i am not able to give a exact problem. — rahularyansharma, Mar 24 '14 at 06:20
ok, try to describe in detail exactly what you want from just the first row of your sample data with ID=1. — Rory, Mar 24 '14 at 11:12
I have done this by self by using UDF. i have posted this as answer as its not possible to this as comments. THanks for your time and support. — rahularyansharma, Mar 24 '14 at 11:14

score 0 · Answer 2 · answered Mar 24 '14 at 11:14

CREATE TABLE #RegionDetail
 (
   Id int identity(1,1) not null,
   RegionId nvarchar(50),
   Zone nvarchar(50) 
 )

INSERT INTO #RegionDetail (RegionId,Zone) values ('1,2,3','A,B')
INSERT INTO #RegionDetail (RegionId,Zone) values ('1,2,3','X,Y')
INSERT INTO #RegionDetail (RegionId,Zone) values ('4,3,5','A,B')

GO

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

GO 

SELECT Id,RegionId,Zone FROM #RegionDetail

select 
   r.Id,f.items as RegionId,z.items as Zone
from 
    #RegionDetail r
    cross apply [dbo].[Split](r.RegionId,',') f
    cross apply [dbo].[Split](r.Zone,',') z
order by Id,RegionId,Zone

Table from comma separated Column at two level

2 Answers2