sql - Split string into multiple rows with multiple columns in paired

Question

I have a table as below:

Order No | Customers            | Amount    
---------+----------------------+-------------
1        | Briant~~Luck         | 23~~2122 
2        | Mike~~Lee~~David     | 10~~200~37 
3        | Stak                 | 100

With format, each customer has one value in Amount.

I'm trying to figure out how to expand the ~~ delimited values to populate a new customers table, which should look like this:

Order No | Customer             | Amount    
---------+----------------------+---------
1        | Briant               |   23
1        | Luck                 | 2122 
2        | Mike                 |   10 
2        | Lee                  |  200
2        | David                |   37 
3        | Stak                 |  100

How can I do?

Any solution in SQL query, function or cursor is appreciated.

Thanks

Answer 1

I think you could store data as your expected result structure. It is much better.
Btw you could use a split function to get your output

DECLARE @SampleData AS TABLE
(
    OrderNo int,
    Customers varchar(200),
    Amount varchar(200)
)

INSERT INTO @SampleData
(
    OrderNo,
    Customers,
    Amount
)
VALUES
( 1, 'Briant~~Luck','23~~2122'), 
( 2, 'Mike~~Lee~~David','10~~200~~37'),
( 3, 'Stak','100')


SELECT sd.OrderNo, c.[Value] AS Customer, a.[Value] AS Amount
FROM @SampleData sd
CROSS APPLY 
(
    SELECT Pos, Value
    FROM [dbo].[SplitString](sd.Customers,'~~')
) c
CROSS APPLY 
(
    SELECT Pos, Value
    FROM [dbo].[SplitString](sd.Amount,'~~')
) a
WHERE c.Pos = a.Pos
ORDER BY sd.OrderNo

Split function

CREATE FUNCTION [dbo].[SplitString] (@Text varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
   Select Pos = Row_Number() over (Order By (Select null))
        ,Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
   From (Select x = Cast('<x>'+ Replace(@Text,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
   Cross Apply x.nodes('x') AS B(i)
);

Demo link: http://rextester.com/XRX32958