--// 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)
No comments:
Post a Comment