How to right a query for comma separated field

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 2005 Forums

Transact-SQL (2005)

How to right a query for comma separated field

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-02-19 : 06:36:52

Hello All,

I have a table with data like following
Col1 Col2
1 12,15,16
2 85,45

and I want to write a query which can give result like given
Col1 Col2
1 12
1 15
1 16
2 85
2 45

Thanks in advance

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 06:39:09

Look for ParseValues function in this forum and delete the duplicate thread.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 07:58:13

use fnParseList(',', Col7) function for this
search this function in this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting,delimited,lists

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 08:02:52

r try like this


declare @str table(Col1 int, Col2 varchar(32))
insert into @str select 1, '12,15,16' union all select
2, '85,45'

SELECT	col1,	
		replace(SUBSTRING(s.Col2,charindex(',',s.Col2,v.number),abs(charindex(',',s.Col2,charindex(',',s.Col2,v.number)+1)-charindex(',',s.Col2,v.number))),',','')as value
FROM		@str s 
INNER JOIN 	master..spt_values AS v  on  v.Type = 'P'
			AND v.number > 0
			AND v.number <= len(s.Col2)
WHERE substring(',' + s.Col2, v.number, 1) = ','

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-02-19 : 08:50:47

Thanks you all,
I have got the solution by the help of your mail.

khufiamalik
Posting Yak Master

120 Posts

Posted - 2009-02-19 : 08:50:48

Thanks you all,
I have got the solution by the help of your mail.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources