Query Help! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query Help!

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-27 : 11:03:55

Hello Friends,
I have a comma separated list of integers and a table named "Pages" that contains 3 columns
1. pageId
2. Name
3. fk_topic( of type integer ) ( comma separated list actually contains the fk_topic)

Now my requirement is i wanted two columns "Topic" and "PageId".

For example:

comma seperated list contains: 45,55,98
Pages table data:
pageId-----Name-----fk_topic
1----------abc------45
2----------abc------45
3----------abc------45
4----------abc------55
5----------abc------55
6----------abc------55
7----------abc------55
8----------abc------98
9----------abc------98

OUTPUT:

TOPIC--------PAGEID
45-----------1
45-----------2
45-----------3
55-----------4
55-----------5
55-----------6
55-----------7
98-----------8
98-----------9

THANKS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-27 : 11:09:00

make use of CSVTable or fnParseList

KH
[spoiler]Time is always against us[/spoiler]

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-27 : 11:27:36

Can anybody post the sample query?

Thanks.

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-27 : 13:11:57

Its Urgent, please post a sample query

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-28 : 02:12:29

try like this


declare @str1 table( str1 varchar(64))
insert into  @str1 select 'M001111,M001222,M001333'

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

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-07-28 : 04:05:35

Thanks a million bklr.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-28 : 05:04:12

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources