Evo primera sa northwindom u SQL 2000. Malo sredi kod ali to je to u principu, bez kursora. Možda postoji još bolji način ali ovo mi je prvo palo na pamet. Inače ovo je jedan od načina sa while petljom da izbegavaš kursore.
Code:
use northwind
declare @customerid char(5)
declare @orderid int
declare @orders varchar(255)
create table #Podaci
(
Customer char(5)
, orders varchar(255)
)
select customerid, orderid into #porudzbenice from orders
order by 1,2
while (select count(*) from #porudzbenice) > 0
begin
select @customerid = min(customerid) from #porudzbenice
select @orderid = min(orderid) from #porudzbenice where customerid = @customerid
if exists(select customer from #podaci where customer = @customerid)
begin
select @orders = orders from #podaci where customer = @customerid
update #podaci
set orders = @orders + ', ' + cast(@orderid as varchar(255))
where customer = @customerid
end
else
begin
insert into #podaci (customer, orders)
select @customerid, cast(@orderid as varchar(255))
end
delete from #porudzbenice where customerid = @customerid and orderid = @orderid
end
select * from #podaci
select customerid, orderid from orders
order by 1,2
drop table #podaci
drop table #porudzbenice
You can fool some people sometimes,
But you can't fool all the people all the time. (Bob Marley)
interesantan upit
