values separation

Question

values separation

venki_1276

Mr or Mrs. 500

Points: 512
More actions
June 17, 2009 at 8:24 am

#206566

hi
I have table like
ID NAME
--- ------
1 aaa
1 bbb
1 ccc
1 ddd
I need result Like
aaa,bbb,ccc,ddd
Than'q

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

See if this helps: http://www.sqlservercentral.com/Forums/Topic735882-338-1.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

--creating test table

create table test (id int, string varchar(100))

go

--inserting demo data

insert into test (id, string)

select 1, 'aaa'

union

select 1, 'bbb'

union

select 2, 'ccc'

union

select 3, 'ddd';

--using for xml cluse with path mode

--in order to create a string with

--all the strings gouped by id.

--The left function is used to leave

--the last coma out from the results

with MyCTE as (

select distinct id, (select string + ','

from test as t

where t.id = test.id

for xml path('')) as Strings

from test)

select id, left(strings, len(strings)-1)

from MyCTE

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

zubamark Right there with Babe Points: 735 More actions · Answer 3

select distinct stuff((select ',' + Rtrim(name) from Your_Table where id=1 FOR XML PATH('')),1, 1,'')name

OTF SSCertifiable Points: 6104 More actions · Answer 4

I whipped this up on a Sql 2008 instance. I'm not sure if it will work on 2005.

CREATE TABLE #ConcatValues

(

value VARCHAR(10)

);

GO

INSERT #ConcatValues

SELECT 'aaa'

UNION ALL

SELECT 'ccc'

UNION ALL

SELECT 'bbb'

UNION ALL

SELECT 'ddd'

GO

DECLARE @values VARCHAR(MAX)

SELECT @values = COALESCE(@values + ', ' + value, value)FROM #ConcatValues ORDER BY value;

SELECT @values;

GO

DROP TABLE #ConcatValues;