Create row with every CSV value

Question

ID          Dates                                          Qty      Secs    DayPart

CO138491-02 06/10/2013,06/11/2013,06/12/2013,06/13/2013    4        6.00    Morning

How can I change those comma separated values and have result like this without using CTE coz i am on SQL SERVER 2000, and its better if we don't use a loop coz latter the data is going to be huge

CO138491-02 06/10/2013  4   6.00    Morning
CO138491-02 06/11/2013  4   6.00    Morning
CO138491-02 06/12/2013  4   6.00    Morning
CO138491-02 06/13/2013  4   6.00    Morning

Any hint would be appreciated or any link to limit my search

Answer 1

Try this

SELECT          a.ID,
                SUBSTRING(',' + a.Dates + ',', n.Number + 1, CHARINDEX(',', ',' + a.Dates + ',', n.Number + 1) - n.Number - 1) AS [Value]
                , [Qty], [Secs], [DayPart]
FROM            Table1 AS a
INNER JOIN      master..spt_values AS n ON SUBSTRING(',' + a.Dates + ',', n.Number, 1) = ','
WHERE           n.Type = 'p'
                AND n.Number > 0 
                AND n.Number < LEN(',' + a.Dates + ',')

Check the below link for reference

http://www.codeproject.com/Questions/526739/ConvertplusColumnplusdataplusintoplusRowsplusthrou