Nitin.Gawade Nitin.Gawade - 2 months ago 6
SQL Question

We need to split data for two columns in sql server

We need to split data for two columns in sql server.

CREATE TABLE #t (Id int,Number varchar(32),Name varchar(32));

Insert Into #t(Id, Number,Name)
Values(123, '1,2,3','a~b~c') ,
(456, '1,2,3,4','a~b~c~d'),
(789, '1,2','a~b')

SELECT *FROM #t


Input:

Id Number Name
123 1,2,3 a~b~c
456 1,2,3,4 a~b~c~d
789 1,2 a~b


We need output in below format:

Id Number Name
123 1 a
123 2 b
123 3 c
456 1 a
456 2 b
456 3 c
456 4 d
789 1 a
789 2 b

Answer

With the help of a parser and a Cross Apply

Select A.ID
      ,B.*
 From  #t A
 Cross Apply (  Select Number=C.RetVal
                      ,Name  =D.RetVal
                  From (Select * from [dbo].[udf-Str-Parse](A.Number,',')) C
                  Join (Select * from [dbo].[udf-Str-Parse](A.Name,'~')) D
                    on (C.RetSeq=D.RetSeq)
             ) B

Returns

ID  Number  Name
123 1       a
123 2       b
123 3       c
456 1       a
456 2       b
456 3       c
456 4       d
789 1       a
789 2       b

The UDF if needed

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

Edit If you Can't use the UDF - We can move the UDF code into the two sub-queries to produce the same results

Select A.ID
      ,B.*
 From  #t A
 Cross Apply (  Select Number=C.RetVal
                      ,Name  =D.RetVal
                  From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                            From (Select x = Cast('<x>'+ Replace(A.Number,',','</x><x>')+'</x>' as xml).query('.')) as A 
                            Cross Apply x.nodes('x') AS B(i)
                        ) C
                  Join (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                            From (Select x = Cast('<x>'+ Replace(A.Name,'~','</x><x>')+'</x>' as xml).query('.')) as A 
                            Cross Apply x.nodes('x') AS B(i)
                        ) D
                    on (C.RetSeq=D.RetSeq)
             ) B