Create Table Test15(
iBillNo int,
mPrice Decimal(8,1),
cperNums Decimal(8,1)
)insert into Test15
select 68104 , 35.7000 , 1.00 union all
select 68105 , 29.0000 , 1.00 union all
select 68105 , 35.7000 , 12.00 select * from test15
-----------------------------------
-------------下面是一條語句實現----------
select ibillno,
mprice1=(select top 1 mprice from test15 where ibillno=a.ibillno),
cpernums1=(select top 1 cpernums from test15 where ibillno=a.ibillno),
mprice2=isnull((select mprice from test15 where ibillno=a.ibillno and
mprice not in (select top 1 mprice from test15 where ibillno=a.ibillno)),0),
cpernums2=isnull((select cpernums from test15 where ibillno=a.ibillno and
mprice not in (select top 1 mprice from test15 where ibillno=a.ibillno)),0)
from test15 a group by ibillno------------------結束------------------------------------------
iBillNo int,
mPrice numeric(10,4),
cPerNums numeric(10,2))
goinsert into tabname values(68104 ,35.7000 ,1.00 )
insert into tabname values(68105 ,29.0000 ,1.00 )
insert into tabname values(68105 ,35.7000 ,12.00)
goselect identity(int,1,1) as ID,a.* into #t from tabname a order by iBillNoselect
(c.ID-d.ID+1) as ID,
c.iBillNo,
c.mPrice,
c.cPerNums
into #t1
from
#t c,
(select iBillNo,min(ID) as ID from #t group by iBillNo) d
where
c.iBillNo = d.iBillNodeclare @s varchar(8000)
declare @i varchar(3)set @s = ''
select @i = max(ID) from #t1 while @i>0
begin
set @s = ',[mPrice'+@i+'] = sum(case when a.ID='+@i+' then a.mPrice else null end)
,[cPerNums'+@i+']= sum(case when a.ID='+@i+' then a.cPerNums else null end)' +@s
set @i = cast((cast(@i as int) - 1) as varchar(3))
endexec('select a.iBillNo'+@s+' from #t1 a group by a.iBillNo')drop table tabname
drop table #t1
drop table #t
一条语句有点难为人,还是按楼上的做吧
declare @Str varchar(3000)
set @Str=''
select @Str=@Str+','+'sum(case iBillNo when '''+iBillNo+''' then cPerNums else 0 end) ['+'cPerNums'+cast(id as varchar)+']'
from (select iBillNo,id from #tmp)a
select @Str=@Str+','+'sum(case iBillNo when '''+iBillNo+''' then mprice else 0 end) ['+'mPrice'+cast(id as varchar)+']'
from (select iBillNo,id from #tmp)a
exec('select iBillNo '+@Str+' from #tmp group by iBillNo')
go
drop table #tmp
go
go
declare @Str varchar(3000)
set @Str=''
select @Str=@Str+','+'sum(case iBillNo when '''+iBillNo+''' then cPerNums else 0 end) ['+'cPerNums'+cast(id as varchar)+']'
from (select iBillNo,id from #tmp)a
select @Str=@Str+','+'sum(case iBillNo when '''+iBillNo+''' then mprice else 0 end) ['+'mPrice'+cast(id as varchar)+']'
from (select iBillNo,id from #tmp)a
exec('select iBillNo '+@Str+' from #tmp group by iBillNo')
go
drop table #tmp
go
create table tabname(
iBillNo int,
mPrice numeric(10,4),
cPerNums numeric(10,2))
insert into tabname values(68104 ,35.7000 ,1.00 )
insert into tabname values(68105 ,29.0000 ,1.00 )
insert into tabname values(68105 ,35.7000 ,12.00)
go--处理
select identity(int,1,1) as ID,a.* into #t from tabname a order by iBillNo
declare @i varchar(10),@s varchar(8000)
select top 1 @s='',@i=count(*)
from #t group by iBillNo order by count(*) desc
while @i>0
select @i=@i-1
,@s=',mPrice'+@i+'=sum(case a.id-b.id when '
+@i+' then a.mPrice else 0 end),cPerNums'
+@i+'=sum(case a.id-b.id when '
+@i+' then a.cPerNums else 0 end)'+@s
exec('
select a.iBillNo'+@s+'
from #t a,(select iBillNo,id=min(id) from #t group by iBillNo)b
where a.iBillNo=b.iBillNo
group by a.iBillNo')
drop table #t
go--删除测试
drop table tabname/*--测试结果
iBillNo mPrice0 cPerNums0 mPrice1 cPerNums1
----------- ----------- ----------- ----------- -----------
68104 35.7000 1.00 .0000 .00
68105 29.0000 1.00 35.7000 12.00--*/