SQL Puzzle #4 - Comma separated value split up without using loop.

We have a table variable named @TableVar

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Qn: Write a query to get the result as shown below without using loop.

  Data
-------------
2012-05-01
2012-05-05
2012-04-13


Ans :

DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data
FROM @XML.nodes('/Root/Child') DOC(COL)




Another Method :

--// Creating Table variable
Declare @TableVar Table
(
Data varchar(max)
)

--// Inserting data to table variable
Insert into @TableVar values('01-May-2012,05-May-2012,13-Apr-2012')

Declare @Str AS Varchar(Max)
Select @Str = 'Select ' +'''' + REPLACE (Data,',',' ''UNION Select ''')  FRom @TableVar
Set @Str = @Str + ''''
Exec (@Str)