分栏显示并排序且加小计与合计,请各位帮忙
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
Chair Blue 50.0 Chair Blue 40.0
Chair Blue 60.0 Chair Blue 30.0
Chair Blue 70.0
Chair 小计 250.0 NULL NULL NULL
Chair1 RED 30.0       Chair1 RED 20.0
Chair1 RED 40.0       Chair1 RED           60.0    
Chair1     小计 150.0 NULL NULL NULL
vsum 合计 400.0 NULL NULL NULL

---由于数据是不定性的,在#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,qty