Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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