SQL server 将游标查询结果显示在一个表中，现在是每一条显示一个表头

Question

SQL server 将游标查询结果显示在一个表中，现在是每一条显示一个表头，语句该如何修改呢？

declare @addr varchar(128)
declare myCursor CURSOR FOR select distinct cCardCode from dbo.ZLKCardFlow
open myCursor

fetch next from myCursor into @addr
while(@@fetch_status=0)
begin

 select distinct  c.cCardCode,c.cName, c.cstatus,  a0 as '充值金额',b1 as '消费金额',(a.a0-b.b1) as '余额' from 

(select SUM(nMoney) a0 from dbo.ZLKCardFlow where cCardCode=@addr and cOPType='充值')a,

(select SUM(nMoney) b1 from dbo.ZLKCardFlow where cCardCode=@addr and cOPType='消费')b,
(select top 1 * from dbo.ZLKCardFlow where cCardCode=@addr order by cstatus desc )c

  fetch next from myCursor into @addr 

end
CLOSE myCursor

DEALLOCATE myCursor

Answer 1

您可以将表头显示语句移至游标外部，则每个表头只显示一遍。修改过的代码如下所示：

declare @addr varchar(128)

declare myCursor CURSOR FOR select distinct cCardCode from dbo.ZLKCardFlow

open myCursor

select '卡号','姓名','状态','充值金额','消费金额','余额'

fetch next from myCursor into @addr

while(@@fetch_status=0)

begin

select distinct c.cCardCode,c.cName, c.cstatus, a0 as '充值金额',b1 as '消费金额',(a.a0-b.b1) as '余额' from
(select SUM(nMoney) a0 from dbo.ZLKCardFlow where cCardCode=@addr and cOPType='充值')a,
(select SUM(nMoney) b1 from dbo.ZLKCardFlow where cCardCode=@addr and cOPType='消费')b,
(select top 1 * from dbo.ZLKCardFlow where cCardCode=@addr order by cstatus desc )c

fetch next from myCursor into @addr

end

CLOSE myCursor

DEALLOCATE myCursor