SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


insert multiple rows into variable


insert multiple rows into variable

Author
Message
megan.beebe
megan.beebe
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 13
Comments posted to this topic are about the item insert multiple rows into variable
Licentiat
Licentiat
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 114
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
Hari.Sharma
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3550 Visits: 455
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
Snookster
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 324
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
Hari.Sharma
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3550 Visits: 455
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
rossano di michele
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 56
the COALESCE function get the first NOT NULL value,
so we don't need the line
select @Numbers = 0, @Strings = ''



greetings
ross
Mithrandir
Mithrandir
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1272 Visits: 184
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
Hardy21
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 1399
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
daryl.scott
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 45
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
btaylor 78431
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 18
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search