ok, whoa, just picked this one up. usually variable assignment from a select statement is thought of as returning one final *single* value (whatever that happens to be).
if instead you perform some logical operations a variable in the meantime you can also continuously calculate and update a variable based on each subsequent row returned.
here's a simple example with simple math...
create table test
(
a int
)
insert into test values (1)
insert into test values(4)
insert into test values(10)
declare @a int
select @a = 0
select @a = @a + a from test
select @a
returns "15"
you have to mind the first value returned, to make sure your statement evaluates as desired (using nulls or not). alright here's something useful -- this process makes concatenating column data into a single line easy (think enumerated list of comma delimited values). here's how...
-- this statement concatenates rows into a single value
-- by performing a variable assignment (for each row returned).
-- assuming you have a table w/ column "col1" as varchar here...
declare @x varchar(1000)
-- make this null if needed
-- this forces COALESCE to skip the comma on the first row.
set @x= null
select @x = COALESCE(@x + ', ', '') + CAST(col1 AS varchar(1000))
from MyTable where col1 is not null and len(col1)>0
select @x
so you've just 'pivoted' the column data from col1 into a single (comma delimited) row. rather handy when needed, and you can forgo using a cursor.