Put all recordset into 1 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 2000 Forums

SQL Server Development (2000)

Put all recordset into 1 field

Author

Topic

KinYeung
Starting Member

14 Posts

Posted - 2006-06-28 : 00:40:11

Hi all,

I want to search a field from table and get a set of record.
But I want to put all the records into a field.

For example, I use the following sql

select distinct(a) from tbl_a where ....
and get the following result
a
=======
apple
angle
abc123

but I want to show the result as
a
=======
apple,angle,abc123

How to modify the sql and get the result?
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-28 : 00:51:25

see here http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 02:18:31

Note that you may face problems if concatenated string exceeds 8000 characters. If you use Front end application do the concatenation there

Madhivanan

Failing to plan is Planning to fail

KinYeung
Starting Member

14 Posts

Posted - 2006-06-29 : 00:16:41

Thanks all

Is it possible to do this within a query. coz it's not allow me to create any function in the server (peoplesoft database)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 00:26:18

quote:
Originally posted by KinYeung

Thanks all

Is it possible to do this within a query. coz it's not allow me to create any function in the server (peoplesoft database)

Just use the technique in the link and create it in a store procedure

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-29 : 00:31:52

Here it is. No function required.

-- Prepare the table & data for testing
declare @tbl_a table
(
	a	varchar(10)
)
insert into @tbl_a
select	'apple'	union all
select	'angle'	union all
select	'abc123'

-- Query
declare @result varchar(8000)

select	@result = coalesce(@result + ',' + a, '' + a)
from	@tbl_a

print	@result
/* RESULT

apple,angle,abc123

*/

KinYeung
Starting Member

14 Posts

Posted - 2006-06-29 : 03:01:44

Got it. thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources