CODEWITHSUNDEEP
sqlsql-server
Matt
Matt

Reputation: 1

I have two columns with multiple values that I need to parse out

I have a table like so:

Make       Model                    Year
----------------------------------------------------    
Toyota     Corolla, Camry, Avalon   2009, 2006, 2011

And I would like it to be the following:

Make       Model      Year
------------------------------    
Toyota     Corolla    2009
Toyota     Camry      2006
Toyota     Avalon     2011

Essentially I would like two columns split up and associated with each other by the order in which they are listed.

Upvotes: 0

Views: 50

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

A parse function which returns a sequence would help here

Example

Select A.Make
      ,B.*
 From  YourTable A
 Cross Apply (
                Select Model=B1.RetVal
                      ,Year =B2.RetVal
                 From  [dbo].[tvf-Str-Parse](A.Model,',') B1
                 Join  [dbo].[tvf-Str-Parse](A.Year,',')  B2
                   on  B1.RetSeq=B2.RetSeq
             ) B

Returns

Make    Model   Year
Toyota  Corolla 2009
Toyota  Camry   2006
Toyota  Avalon  2011

The UDF if interested

CREATE FUNCTION [dbo].[tvf-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((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);

Upvotes: 4

Related Questions