0

I have a table with somthing like this.

     Column1                              Column2

     Salade, Tomato,                       FOOD
     Earth, Water, Fire                   Element

I want to have a view that looks like this.

   Column1                Column2
  Salade                   FOOD
  Tomato                   FOOD
  Earth                    Element
  Water                    ELEMENT

Currently I have made a work arround with a cursor that loops true this table. Inserts in another table...

Would this be posible without a cursor? In 1 select statement without creating another table.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Frederik De Clercq
  • 367
  • 3
  • 14

1 Answers1

1

I would like to use of XML node method to split your comma separated data 

SELECT LTRIM(a.value('.', 'nvarchar(max)')) Column1, Column2
FROM
(
    SELECT CAST('<m>'+REPLACE([Column1], ',', '</m><m>')+'</m>' AS XML) AS [Column1],
           [Column2] FROM <table>
) m CROSS APPLY Column1.nodes('/m') AS split(a);

Result :

Column1 Column2
Salade  FOOD
Tomato  FOOD
Earth   Element
Water   Element
Fire    Element
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52