0

I am trying to convert my column value into multiple rows. As an example please take a look

LoadName          LoadNumber
------------      -----------------------
N-2               9753328  9753352  9753368  9754543  9755035
N-54              L00200258 L00200285

OutPut for above data will be

 LoadName           LoadNumber
 --------           ----------
   N-2                9753328
   N-2                9753352
   N-2                9753368
   N-2                9754543
   N-2                9755035
   N-54               L00200258
   N-54               L00200285

What i have tried

 SELECT A.[LoadName],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [LoadName],  
         CAST ('<M>' + REPLACE([LoadNumber], ' ', '</M><M>') + '</M>' AS XML) AS String  
     FROM  MLoad) AS A 
     CROSS APPLY String.nodes ('/M') AS Split(a) ;

I have made the query at DB Fidder. What i need to do for the same result. Thanks for your help

A.Goutam
  • 3,261
  • 8
  • 36
  • 85

0 Answers0