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

bvr · Accepted Answer · 2013-06-26 09:15:49Z

4

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

answered Jun 26, 2013 at 9:15

bvr

4,8361 gold badge23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexander Sigachov · Accepted Answer · 2013-06-26 08:41:04Z

1

If you don't want use CTE or loops, try use approach with additional table of numbers:

See http://www.sommarskog.se/arrays-in-sql-2000.html#tblnum-core

Something like:

CREATE FUNCTION inline_split_me (@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(',' + @param + ',', Number + 1,
                    charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(',' + @param + ',') - 1
            AND  substring(',' + @param + ',', Number, 1) = ',')

answered Jun 26, 2013 at 8:41

Alexander Sigachov

1,55111 silver badges16 bronze badges

2 Comments

Sandip Bantawa Over a year ago

Actually I don't want a splitter rather each value with splitted strings, in addition since its a huge data is it wise to use a function??

Alexander Sigachov Over a year ago

I do not know another way, except function in this case. The most effective way is use of CLR function, but it dosn't work in MSSQL2000

Collectives™ on Stack Overflow

Create row with every CSV value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related