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