[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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.


0
yechan
Asked:
yechan
  • 4
  • 3
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
try this:

declare @result as varchar(8000)

select distinct @result = isnull(@result + ', ', '') + fName  from dbo.Person order by fName

print @result
0
 
yechanAuthor Commented:
Hi Emoreau,

for one reason or another, the @result variable only holds the very last value (i.e. Zach).

0
 
yechanAuthor Commented:
I *think* I remember seeing something like this where this is possible but I am not 100% sure.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the field possible char (and not varchar)?
0
 
yechanAuthor Commented:
angel:

the field itself is varchar(50).
0
 
Éric MoreauSenior .Net ConsultantCommented:
remove the distinct clause
0
 
yechanAuthor Commented:
@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?
0
 
Éric MoreauSenior .Net ConsultantCommented:
I am not exactly sure of the effect of the Distinct clause here
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now