You can split the string in SQL based on that character. There are a few ways of doing that. This one uses FOR XML ...
Splitting Delimited Strings Using XML in SQL Server[
^]
And this one (which is the one I use) actually uses a WHILE loop (but don't tell anyone!)...
How to Split a string by delimited char in SQL Server.............. - SQLServerCentral[
^]
The problem then becomes how to merge the data in the way you want. I ended up with this really awful query
declare @tab table (claimid int, amount nvarchar(50), drive nvarchar(50))
declare @inc int = 1
declare @top int = (SELECT MAX(claimid) FROM ClaimDetails)
WHILE @inc <= @top
BEGIN
declare @amt nvarchar(50), @drv nvarchar(50)
SELECT @amt = amount, @drv = drive FROM ClaimDetails WHERE claimid = @inc
declare @tab1 table (rn int identity(1,1), claimid int, amount nvarchar(50))
declare @tab2 table (rn int identity(1,1), claimid int, drive nvarchar(50))
INSERT INTO @tab1 SELECT @inc, splitdata as amount FROM dbo.fnSplitString(@amt, '/')
INSERT INTO @tab2 SELECT @inc, splitdata as amount FROM dbo.fnSplitString(@drv, '/')
INSERT INTO @tab
SELECT @inc, amount, drive
FROM @tab1 T1
LEFT JOIN @tab2 T2 ON T1.claimid=T2.claimid AND T1.rn=T2.rn
delete @tab1
delete @tab2
SET @inc += 1
END
SELECT * FROM @tab
It gets the results you want, but I think that process is best left to your UI layer if at all possible
[UPDATE]
As requested, here is an explanation of this query step by step
I declared a table variable
@tab
to hold the final results. It should be identical in form to your ClaimDetails table.
Next up are variables
@inc
(short for "increment") and
@top
(short for "top value of claimed"). While writing this explanation I realised that I had @top = COUNT... but I should be reading the MAX value - I've corrected the code above. Those variables just control the WHILE loop which is going to step through the entire ClaimDetails table row by row (loops are not usually necessary but your requirements are somewhat unique).
Inside that loop we are grabbing some information from each row,
one row at a time... so first time through the loop the line
SELECT @amt = amount, @drv = drive FROM ClaimDetails WHERE claimid = @inc
gets the row for claimid = 1 and sets the variable
@amt
to the value
10/11
and @drv to the value
car/bike
.
We can then pass those values into the String split function from the link I included above. I'm not going to explain that as the author does that himself. The main point is that we can't do this in a
SELECT
statement directly because the function returns a
table of results
The table variables
@tab1
and
@tab2
will contain the results from splitting those strings. The
rn
column on each will automatically assign a "row number" which we will use later to match up the '10' with 'car' and the '11' with 'bike'.
So after the first loop
@tab1
contains
rn claimid amount
1 1 10
2 1 11
and
@tab2
contains
rn claimid drive
1 1 car
2 1 bike
The next bit takes these two tables and matches up rn = 1 on @tab1 (value '10') with rn = 1 on @tab2 (value 'car') and then rn = 2 (value '11') with rn = 2 on @tab2 (value 'bike'). To be honest, you don't actually need
T1.claimid=T2.claimid
because we're in a loop and the only data we have is from a single claimid for each iteration of the loop.
So after the first loop @tab contains
claimid amount drive
1 10 car
1 11 bike
You have to delete the contents of the table variables for each loop otherwise the data gets doubled up - even though the variables are declared within the loop!
Obviously don't forget to move the loop counter on
SET @inc += 1
to avoid an infinite loop. That line is the same as
SET @inc = @inc + 1
When the loop is complete the table variable
@tab
contains the data split the way you described in your post
claimid amount drive
1 10 car
1 11 bike
2 11 car
Note that the second row also contains the claimid
1
. You cannot return results the exact way you described in your post, you definitely have to handle that in the UI layer.