Is this possible......??

Say I have a table with one field and there are 26 records in it... A, B, C, D, E, ... etc.

If I do a select statement on that table I get 26 records, one for each field. (select fieldName from tblName)

Is it possible to set a variable equal to a single string off of the select statement and delimit it with a chosen delimiter (ie "A,B,C,D,E,F......")

Thank You for the help!!!With which DBMS? There is no standard SQL answer to this.|||Using MS SQL 7|||Looking for something like

set @.stringName = (select fieldName & ',' from tblName)

So that @.stringName is set to a string 'A,B,C,D,E,....'|||What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str|||Originally posted by snail
What about this?

drop table test
create table test(id int identity,code varchar(10))
go
insert test(code) values('a')
insert test(code) values('b')
insert test(code) values('c')
insert test(code) values('d')
insert test(code) values('e')
go
declare @.str varchar(8000)
set @.str=''
select @.str=@.str+code from test
select @.str That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.|||Originally posted by gman_gsxr750
That's pretty much what I'm looking for but I don't understand how your '+ code from test' is going to work. That piece should be my recordset

set @.str = @.str + (select fieldName from tblName)

something like that where my recordset can be turned into a string.

Just try and you'll see...|||Originally posted by snail
Just try and you'll see... Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!|||Originally posted by gman_gsxr750
Holy moley!!! I've never seen that before!!!

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

One last question (only because I've never used the code in that way before...

can I put a conditional on it

set @.str = @.str + code from table (where id < 100)

or something like that?

And did I mention..... Thank you!

Why not?|||Originally posted by snail
Why not? Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?|||Originally posted by gman_gsxr750
Ever get that rush when something finally goes your way and things work out?

Thank you soooooooooo much! I got the conditional to work as well. I just need to play with this a little to figure out the nuances.

Do you know what that kind of query is called so I can reference?

I have no idea...|||Originally posted by snail
I have no idea... OK, last question, hopefully you can help me with this.

Here's my code:
declare @.idpeople int
set @.idpeople = 200002
declare @.str varchar(8000)
set @.str = ''

select @.str = @.str + ',' + ideventcode from tblPeopleEvents where idpeople = @.idpeople

let's say this returns the following ',CXL,AS' (two codes CXL and AS)

this works fine, but if I add an order by clause to it (so it returns AS,CXL instead), I only get one of the two values (CXL)

any ideas on how far I can take the select portion (where, order by, group by, etc)?|||it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)|||OK, here's my final code. I used a subquery to get the result set the way I needed it. Much thanks to Snail for the help. And to r937 for the sarcasm ;).

create procedure spGetEventString
@.idpeople int,
@.eventString varchar(255) OUTPUT

as

set @.eventString = ''

-- Create string of Event codes
-- use sub query to order result set
select @.eventString = @.eventString + rTPE.ideventcode
from
(
select top 100 idEventCode + ',' as idEventCode
from tblPeopleEvents
where idpeople = @.idpeople
order by idEventCode
) as rTPE

--Remove Trailing Comma
set @.eventString = left(@.eventString,len(@.eventString)-1)

return|||Originally posted by r937
it's called a magic query

it works, and it produces the result by magic

hey snail, where's the comma between values?

;)

I am not a magician I am only learning... ;)|||I think it should be called the Loophole query, because it doesn't look like it should work, but it does.|||Originally posted by blindman
I think it should be called the Loophole query, because it doesn't look like it should work, but it does.

That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593|||Originally posted by Enigma
That is a bit harsh ...

http://www.dbforums.com/showthread.php?threadid=979593 Yep, that's it. Works like a charm too!|||This type of query is the coolest thing I've learned from DBForums, but I haven't seen any Microsoft Documentation that talks about it. That's why it seems like a loophole to me.

Does anybody know of any BOL or MS Support references regarding this self-referential query?|||Thats what it should be called -->"A self-referential query"|||and it even does what Enigma was asking in the referenced post:

select @.str=@.str+case @.str when '' then '' else ',' end + code from test