Stuff and xml path - 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

Stuff and xml path

Author

Topic

Lesombrero
Starting Member

43 Posts

Posted - 2013-04-14 : 04:38:23

Hello everybody,
I have this problem:

Table Cat
id | name
----- | -------
1 | AAAAA
2 | BBBBB
3 | CCCCC
4 | DDDDD
5 | EEEEE
6 | FFFFF

Table Prod
id_prod | id_cat
------- | -------
1 | 1,5
2 | 3,2,6
3 | 2,5
4 | 4,6
5 | 2,4,6
6 | 2,4

I expect the result as follow:
Category
--------
AAAAA, EEEEE
CCCCC, BBBBB, FFFFF
BBBBB, EEEEE
....

I tried with stuff and xml path, but can't get what I want!
Thanks in advance for your help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-14 : 13:26:25

quote:
Originally posted by Lesombrero

Hello everybody,
I have this problem:

Table Cat
id | name
----- | -------
1 | AAAAA
2 | BBBBB
3 | CCCCC
4 | DDDDD
5 | EEEEE
6 | FFFFF

Table Prod
id_prod | id_cat
------- | -------
1 | 1,5
2 | 3,2,6
3 | 2,5
4 | 4,6
5 | 2,4,6
6 | 2,4

I expect the result as follow:
Category
--------
AAAAA, EEEEE
CCCCC, BBBBB, FFFFF
BBBBB, EEEEE
....

I tried with stuff and xml path, but can't get what I want!
Thanks in advance for your help.

select
	stuff((select ',' + name from cat c where
	','+p.id_cat+',' like '%,'+cast(c.id as varchar(32)) + '%,'
	for xml path('')),1,1,'') as Category
from
	prod p

Lesombrero
Starting Member

43 Posts

Posted - 2013-04-15 : 02:57:14

Thank you so much James. This is perfect!

Lesombrero
Starting Member

43 Posts

Posted - 2013-04-15 : 03:33:54

Ouups! Sorry!
I just noticed something. 11 is treated as 1,11 !!!
Any suggestion?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 03:41:03

small typo is there...


stuff((select ',' + name from @Cat c where
	','+p.id_cat+',' like '%,'+cast(c.id as varchar(32)) + ',%'

--
Chandu

Lesombrero
Starting Member

43 Posts

Posted - 2013-04-15 : 03:48:21

Of Course! Thanks Chandu.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 04:36:16

quote:
Originally posted by Lesombrero

Of Course! Thanks Chandu.

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources