convert comma separated values into columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

convert comma separated values into columns

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-01-11 : 14:49:40

I have sql server 2000 and i have data like show below in the table

CREATE TABLE #temp
(orderid int, orderyear varchar(50))

INSERT #temp
SELECT 1,'2009 rejected;2010 rejected' UNION ALL
SELECT 2,'2009 rejected;2010' UNION ALL
SELECT 3,'2009' UNION ALL
SELECT 4,'2010' UNION ALL
SELECT 5,'2007 rejected;2008 rejected;2009'

Now I want the results to be like:

OrderID ORDERYEAR
1 2009
1 2010
2 2009
2 2010
3 2009
4 2010
5 2007
5 2008
5 2009

I need only the numerical part. how do i do this in sql server 2000

-----------------------------------------------------------------------------------------------
Ashley Rhodes

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 15:09:56

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76733

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-11 : 22:50:54

declare @table table(orderid int,orderyear varchar(600) )
insert into @table
SELECT 1,'2009 rejected;2010 rejected' UNION ALL
SELECT 2,'2009 rejected;2010' UNION ALL
SELECT 3,'2009' UNION ALL
SELECT 4,'2010' UNION ALL
SELECT 5,'2007 rejected;2008 rejected;2009'

SELECT s.orderid,
LEFT(SUBSTRING(s.orderyear, v.Number - 1,
COALESCE(NULLIF(CHARINDEX(';', s.orderyear, v.Number), 0), LEN(s.orderyear) + 1) - v.Number + 1),4) AS value
FROM @table AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING(';_' + s.orderyear, v.Number, 1) = ';'

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources