insert multiple rows into variable

Question

Post reply

insert multiple rows into variable

megan.beebe

SSC Veteran

Points: 250
More actions
July 10, 2008 at 9:50 am

#184500

Comments posted to this topic are about the item insert multiple rows into variable

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Licentiat SSC Enthusiast Points: 146 More actions · Answer 1

This will give you the string in the right format:

--select all the rows into the variables

select

@Numbers = @Numbers + iNumber,

@Strings=CASE @Strings WHEN '' THEN sNumber ELSE @Strings + ', '+sNumber END

from @demo

Excellent trick.

Nick

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 2

The Best way to use COALESCE function because its very fast compare to other techniques.

select @Numbers = 0, @Strings = ''

SELECT @Numbers = COALESCE(@Numbers,'')+iNumber,

@Strings = COALESCE(@Strings+',','')+sNumber

FROM @demo

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Snookster Valued Member Points: 53 More actions · Answer 3

your COALESCE example results are ",ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE"

to get "ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,NINE" change the first line to...

select @Numbers = 0, @Strings = NULL

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 4

Yeah James.

I just copied that line from earlier post and didnt changed it.

But you are right.

Cheers,
Hari
Tips & Tricks for SQL BI Developers

rossano di michele SSC Rookie Points: 49 More actions · Answer 5

the COALESCE function get the first NOT NULL value,

so we don't need the line

select @Numbers = 0, @Strings = ''

greetings
ross

Mithrandir SSCrazy Points: 2628 More actions · Answer 6

Beware that this is an unsupported feature from MS.

The results may be much different if you use a complex query to get the string. Happened to me once and it's a pain to debug.

A workaround is to use a temp table to place the data with the complex query and get the results from there using a simple query. Anything to get rid of cursors!

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 7

Mithrandir (7/21/2008)
Beware that this is an unsupported feature from MS.
The results may be much different if you use a complex query to get the string. Happened to me once and it's a pain to debug.
A workaround is to use a temp table to place the data with the complex query and get the results from there using a simple query. Anything to get rid of cursors!

Agree with you.

Also, without ORDER BY clause, it is not guaranteed about Order in which you receive result. Result may be different in good amount of rows.

Thanks

daryl.scott SSC Enthusiast Points: 125 More actions · Answer 8

A comma-delimited list of values can also be generated using the for xml path approach.

select stuff((select ',' + cast(sNumber as varchar(10))

from @demo

for xml path('')),1,1,'');

btaylor 78431 SSC Journeyman Points: 93 More actions · Answer 9

Dude!

This is a feature of TSQL we have utilized to great advantage for years.

You are using set logic to implement the impact of each row upon an already existing variable.

I have used it to generate median, average of average values and much more than simple string concatenation.

For example, if you have a series of monthly averages and wish to get the annual average you can't just take the average of all 12.

The formula is (period + 1) * (Previous Period + 1) -1 performed against all 12 periods. This works great using your select kind of statement by self joining the table against the previous row.

Nice post.

Cheers,

Ben

Boltz442 Old Hand Points: 392 More actions · Answer 10

I took one look at this article and thought "What's so special about this?" It's hardly revolutionary!

I used to call my self The Master on forums, then I entered the IT industry and realised how much I still have to learn.

Manie Verster SSCertifiable Points: 7152 More actions · Answer 11

Boltz42 (5/17/2012)
I took one look at this article and thought "What's so special about this?" It's hardly revolutionary!

I agree. I do not see the purpose for this. You would anyway need a loop to get the values again. :Whistling:

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

daryl.scott SSC Enthusiast Points: 125 More actions · Answer 12

The reason the script is not revolutionary is because other like Erland Sommarskog performed a lot of testing to figure out the best way to do it (http://www.sommarskog.se/arrays-in-sql-2005.html). In SQL Server 2005, a comma-delimited list is a great way to pass primary and foreign key values between stored procedures and between applications and the database.