Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

insert multiple rows into variable Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2008 9:18 AM
Points: 4, Visits: 13
Comments posted to this topic are about the item insert multiple rows into variable
Post #531750
Posted Thursday, July 17, 2008 6:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:18 AM
Points: 46, Visits: 94
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
Post #535892
Posted Thursday, July 17, 2008 6:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, 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
Post #535897
Posted Thursday, July 17, 2008 9:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:14 PM
Points: 24, Visits: 309
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



Post #536117
Posted Thursday, July 17, 2008 10:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 12:43 PM
Points: 1,330, 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
Post #536454
Posted Friday, July 18, 2008 1:35 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 2:30 AM
Points: 1, Visits: 56
the COALESCE function get the first NOT NULL value,
so we don't need the line
select @Numbers = 0, @Strings = ''




greetings
ross
Post #536527
Posted Monday, July 21, 2008 5:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:48 AM
Points: 548, Visits: 167
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!
Post #538064
Posted Wednesday, May 16, 2012 10:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 1,015, Visits: 1,289
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
Post #1301481
Posted Thursday, May 17, 2012 7:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:05 AM
Points: 25, Visits: 40
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,'');

Post #1301725
Posted Thursday, May 17, 2012 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 5, 2012 7:48 AM
Points: 3, 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
Post #1301760
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse