Sajid Wasim Sajid Wasim - 1 year ago 45
SQL Question

Splitting multiple delimited values into multiple rows

I have been looking for a solution in StackOverflow but didn't find anything useful. I am facing a issue and I hope anyone would like to help me out.

I have value like this:

Create table DemoRecords
CustID int identity (1,1),
CustomerName varchar(50),
CurrencyCode varchar(50),
CurrentBalance varchar(50),
DateValue varchar(50)

INSERT INTO DemoRecords VALUES ('Mr. X', 'BDTýUSDýGBP','10500ý2500ý1050','20150101ý20150201ý20150301')

..and I need output like this: (Please take a look at the picture attached below)

enter image description here

Please don't suggest me to use CTE because there are more than 100 columns in that table.

Answer Source

Here is a function to split a string into rows. Below that is a query against your demorecords table that uses the function to get the requested result.

create function dbo.split
    @delimited nvarchar(max),
    @delimiter nvarchar(5)
returns @rows table
    rownumber int not null identity(1,1),
    value nvarchar(max) not null
    if @delimited is null return

    declare @delr_len int = len(@delimiter)
    declare @start_at int = 1
    declare @end_at int
    declare @deld_len int

    while 1=1
        set @end_at = charindex(@delimiter,@delimited,@start_at)
        set @deld_len = case @end_at when 0 then len(@delimited) else @end_at-@start_at end
        insert into @rows (value) values( substring(@delimited,@start_at,@deld_len) );
        if @end_at = 0 break;
        set @start_at = @end_at + @delr_len


select custid, customername, currencycode=currencycode.value, currentbalance=currentbalance.value, datevalue=datevalue.value
    from demorecords r
    cross apply (select rownumber, value from dbo.split(r.currencycode,'ý') ) currencycode
    cross apply (select rownumber, value from dbo.split(r.currentbalance,'ý') where rownumber = currencycode.rownumber ) currentbalance
    cross apply (select rownumber, value from dbo.split(r.datevalue,'ý') where rownumber = currencycode.rownumber ) datevalue