Reading values into a variable without using a loop/cursor
Hi Experts,
many months ago I thought I remember seeing sql syntax that would allow me to create a variable and read/assign certain values to it without having to resort to a loop.
Here is my first try:
declare @result as varchar(8000)
select distinct @result = fName + ', ' from dbo.Person order by fName
print @result
I am hoping the @result variable would show me something like this:
Abby, John, Kenneth, etc....
thanks
P.S. Right now this is against SQL Server 2000.
many months ago I thought I remember seeing sql syntax that would allow me to create a variable and read/assign certain values to it without having to resort to a loop.
Here is my first try:
declare @result as varchar(8000)
select distinct @result = fName + ', ' from dbo.Person order by fName
print @result
I am hoping the @result variable would show me something like this:
Abby, John, Kenneth, etc....
thanks
P.S. Right now this is against SQL Server 2000.
ASKER
Hi Emoreau,
for one reason or another, the @result variable only holds the very last value (i.e. Zach).
for one reason or another, the @result variable only holds the very last value (i.e. Zach).
ASKER
I *think* I remember seeing something like this where this is possible but I am not 100% sure.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
is the field possible char (and not varchar)?
ASKER
angel:
the field itself is varchar(50).
the field itself is varchar(50).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@emoreau:
removing the distinct seemed to do the trick. Can you please explain why the distinct keyword would cause only the last value to show?
removing the distinct seemed to do the trick. Can you please explain why the distinct keyword would cause only the last value to show?
I am not exactly sure of the effect of the Distinct clause here
declare @result as varchar(8000)
select distinct @result = isnull(@result + ', ', '') + fName from dbo.Person order by fName
print @result