Comma Separated Values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Comma Separated Values

Author

Topic

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 02:20:26

I have a table which has values as follows
ID CODE DESCRIPTION
1 A1 AAAAAA
2 c3 CCCCCC
3 A1 BBBBBBB

I want the distinct comma separated values for CODE & DESCRIPTION.
i.e
CODEVALUE DESCVALUE
A1,C3 AAAAAA,CCCCCC,BBBBBBB.

Please Help!!!

Rahul Shinde

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-16 : 02:23:34

use xml path()

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 02:26:10

quote:
Originally posted by bklr

use xml path()

Can you provide a code?

Rahul Shinde

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-16 : 02:26:40

declare @tab table (ID int, CODE varchar(32),DESCRIPTION varchar(32))
insert into @tab select 1, 'A1', 'AAAAAA'
union all select 2, 'c3', 'CCCCCC' union all
select 3, 'A1', 'BBBBBBB'

select stuff((select distinct ','+ CODE from @tab for xml path('')),1,1,'')as code ,
stuff((select ','+ DESCRIPTION from @tab for xml path('')),1,1,'') as description

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 02:32:00

quote:
Originally posted by bklr

declare @tab table (ID int, CODE varchar(32),DESCRIPTION varchar(32))
insert into @tab select 1, 'A1', 'AAAAAA'
union all select 2, 'c3', 'CCCCCC' union all
select 3, 'A1', 'BBBBBBB'

select stuff((select distinct ','+ CODE from @tab for xml path('')),1,1,'')as code ,
stuff((select ','+ DESCRIPTION from @tab for xml path('')),1,1,'') as description

Wow, That works just great for me. Thanks a lot

Rahul Shinde

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-16 : 02:32:58

ur welcome

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 02:52:16

you cant guarantee the order of concatenation of values unless you use order by in for xml path query.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 02:56:11

quote:
Originally posted by visakh16

you cant guarantee the order of concatenation of values unless you use order by in for xml path query.

I am not worried about the order of values, but I dont know how to do that. Can you modify the above code?

Rahul Shinde

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-16 : 03:07:38

Use distinct in the select statement for description ..

stuff((select distinct ','+ DESCRIPTION from @tab for xml path('')),1,1,'') as description

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 03:20:49

[code]
select stuff((select distinct ','+ CODE from @tab order by ID for xml path('')),1,1,'')as code ,
stuff((select ','+ DESCRIPTION from @tab order by ID for xml path('')),1,1,'') as description

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources