分栏显示并排序且加小计与合计,请各位帮忙
create table #tt
(
Item varchar(10) ,
Color varchar(10) ,
QtySum float
)
select *,id=identity(int,1,1) into #newtt from #tt
insert #newtt
select 'Chair ', 'Blue ',50 union
select 'Chair ', 'Blue ',40 union
select 'Chair ', 'Blue ',60 union
select 'Chair ', 'Blue ',30 union
select 'Chair ', 'Blue ',70 union
select 'Chair1 ', 'RED ',30 union
select 'Chair1 ', 'RED ',20 union
select 'Chair1 ', 'RED ',40 union
select 'Chair1 ', 'RED ',60
我要生成如下数据,不知如何生成,请指点.
item color qtysum item1 color1 qtysum1
ChairBlue50.0ChairBlue40.0
ChairBlue60.0ChairBlue30.0
ChairBlue70.0
Chair小计250.0NULLNULLNULL
Chair1RED30.0 Chair1RED20.0
Chair1RED40.0 Chair1RED 60.0
Chair1 小计150.0NULLNULLNULL
vsum合计400.0NULLNULLNULL
---由于数据是不定性的,在#newtt表中可能会有更多的数据,意味着color= 'red '的记录数(count)也可能为奇数,color= 'blue '的记录数也可能为偶数.生成最终数据模式是上述模式.
衷心谢谢各位!
[解决办法]
这种分栏在水晶报表中可以做啊
[解决办法]
create table #tt
(
Item varchar(10) ,
Color varchar(10) ,
QtySum float
)
select *,id=identity(int,1,1) into #newtt from #tt
insert #newtt select 'Chair ', 'Blue ',50
insert #newtt select 'Chair ', 'Blue ',40
insert #newtt select 'Chair ', 'Blue ',60
insert #newtt select 'Chair ', 'Blue ',30
insert #newtt select 'Chair ', 'Blue ',70
insert #newtt select 'Chair1 ', 'RED ',30
insert #newtt select 'Chair1 ', 'RED ',20
insert #newtt select 'Chair1 ', 'RED ',40
insert #newtt select 'Chair1 ', 'RED ',60
select *,idd=(select count(1) from #newtt where item=a.item and color=a.color and id <=a.id) into #ntt from #newtt a
select item,color,qtysum,idd into #zz from
(
select item,color,qtysum,idd from #ntt
union all
select *,idd=(select case when max(idd)%2=0 then max(idd)+1 else max(idd)+2 end from #ntt where item=aa.item) from(
select item,color, sum(qtysum) dd from #ntt group by item,color with rollup )
aa
where color is null
)bb
order by item,color,idd
select aa.item,isnull(aa.color, '小计 ') color,aa.qtysum,isnull(bb.item, ' ') item1,isnull(bb.color, ' ') color1,isnull(bb.qtysum, ' ') qtysum1 from
(select item,color,qtysum,idd from #zz where idd%2=1) aa
Left Join
(select item,color,qtysum,idd from #zz where idd %2=0) bb
on
aa.item=bb.item and aa.color=bb.color and aa.idd=bb.idd-1
union
select 'vstm ', '合计 ',sum(qtysum), ' ', ' ', ' ' from #newtt
drop table #tt,#newtt,#ntt,#zz
