Sajid Wasim Sajid Wasim - 5 months ago 9x
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.


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