Solved

Reading values into a variable without using a loop/cursor

Posted on 2011-09-26
8
199 Views
Last Modified: 2012-08-13
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
Comment
Question by:yechan
  • 4
  • 3
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36600115
try this:

declare @result as varchar(8000)

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

print @result
0
 
LVL 1

Author Comment

by:yechan
ID: 36600188
Hi Emoreau,

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

0
 
LVL 1

Author Comment

by:yechan
ID: 36600191
I *think* I remember seeing something like this where this is possible but I am not 100% sure.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36600207
is the field possible char (and not varchar)?
0
 
LVL 1

Author Comment

by:yechan
ID: 36600296
angel:

the field itself is varchar(50).
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 125 total points
ID: 36600333
remove the distinct clause
0
 
LVL 1

Author Comment

by:yechan
ID: 36600353
@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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36600396
I am not exactly sure of the effect of the Distinct clause here
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question