22,209
社区成员
发帖
与我相关
我的任务
分享
create table #temp
(
serviceid int,
servicename varchar(100),
vendoramt money,
inidx int
)
--生成测试数据
insert into #temp(serviceid, servicename, vendoramt, inidx)
select 0, 'Rent', 1000, 10
union
select 0, 'Processing Fee', 100, 30
union
select 0, 'Damage Waiver Fee', 200, 20
declare @blockout varchar(5000)
set @blockout = ''
--用select来作累加操作
select @blockout = @blockout + '<tr><td nowrap class=n>'
+ servicename + '</td><td align=right class=m>$'
+ convert(varchar, isnull(vendoramt, 0), 1) + '</td></tr>'
from(
select top 100 PERCENT isnull(t.servicename, '') servicename, sum(t.vendoramt) vendoramt
from #temp t
group by t.servicename, t.serviceid, t.inidx
order by isnull(t.inidx, 10000)
) tmp
print @blockout
drop table #temp