有点复杂!

[loen113]

create table #tmp
(
fday varchar(2),
fitem varchar(30),
fph varchar(10),
fbc varchar(20),
fqty decimal(18,4)
)

insert into #tmp
select '01','S816','第七批','乙班',100
insert into #tmp
select '02','S816','第七批','甲班',600
insert into #tmp
select '02','S816','第七批','乙班',850
insert into #tmp
select '03','S816','第七批','甲班',650



select * from #tmp


/*
想得到以下效果，交叉表好像不行

fday s816|第七批|甲班 s816|第七批|乙班 s816|第七批|合计
01 100 100
02 600 850 1450
02 650 650


*/

drop table #tmp

[loen113]

莫有人吗

[loen113]

我加分

[--小F--]

select  
  fday,
  sum(case when fbc='甲班' then fqty else 0 end) as 's816|第七批|甲班',
  sum(case when fbc='乙班' then fqty else 0 end) as 's816|第七批|乙班',
  sum(fqty) as '合计'
from
  #tmp
group by  
  fday

[loen113]

[Quote=引用 4 楼 fredrickhu 的回复:]
SQL code
select
fday,
sum(case when fbc='甲班' then fqty else 0 end) as 's816|第七批|甲班',
sum(case when fbc='乙班' then fqty else 0 end) as 's816|第七批|乙班',
sum(fqty) as '合计'
from
#tmp
grou……
[/Quote]
哥们儿，你这样就把方法写错了。我需要一个灵活点的方法。
不过谢谢你的回答

[中国风]

create table #tmp
(
  fday varchar(2),
  fitem varchar(30),
  fph varchar(10),
  fbc varchar(20),
  fqty decimal(18,4)  
)

insert into #tmp
select '01','S816','第七批','乙班',100
insert into #tmp
select '02','S816','第七批','甲班',600
insert into #tmp
select '02','S816','第七批','乙班',850
insert into #tmp
select '03','S816','第七批','甲班',650  


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+N','+quotename(fitem+'|'+fph+'|'+fbc)+N'=sum(case when fitem=N'+quotename(fitem,'''')+' and fph=N'+quotename(fph,'''')+N' and fbc=N'+QUOTENAME(fbc,'''')+ ' then fqty else 0 end)'

from #tmp 
group by fitem,fph,fbc

--顯示生成語句
print N'select fday'+@s+N',sum(fqty) as 合计 from #tmp group by fday'

exec(N'select fday'+@s+N',sum(fqty) as 合计 from #tmp group by fday')

go
/*
fday	S816|第七批|甲班	S816|第七批|乙班	合计
01	0.0000	100.0000	100.0000
02	600.0000	850.0000	1450.0000
03	650.0000	0.0000	650.0000
*/

/*
select fday,[S816|第七批|甲班]=sum(case when fitem=N'S816' and fph=N'第七批' and fbc=N'甲班' then fqty else 0 end),[S816|第七批|乙班]=sum(case when fitem=N'S816' and fph=N'第七批' and fbc=N'乙班' then fqty else 0 end),sum(fqty) as 合计 from #tmp group by fday
*/

[中国风]

--SQL2005 

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename(fitem+'|'+fph+'|'+fbc) from #tmp group by fitem,fph,fbc
exec('select * from (select fday,fitem+''|''+fph+''|''+fbc as Col,fqty from #tmp) as a pivot (max(fqty) for Col in('+@s+'))b')

/*
fday	S816|第七批|甲班	S816|第七批|乙班
01	NULL	100.0000
02	600.0000	850.0000
03	650.0000	NULL
*/

[--小F--]

declare @sql varchar(8000)
set @sql = 'select fday '
select @sql = @sql + ' , max(case fbc when ''' + fbc + ''' then fqty else 0 end) [' + fitem+'|'+fph+'|'+fbc + ']'
from (select distinct fbc from tb) as a
set @sql = @sql + ' from tb group by fday'
exec(@sql) 

[--小F--]

declare @sql varchar(8000)
set @sql = 'select fday '
select @sql = @sql + ' , max(case fbc when ''' + fbc + ''' then fqty else 0 end) [' + fitem+'|'+fph+'|'+fbc + ']'
from (select distinct fbc from tb) as a
set @sql = @sql + ',sum(qty) as ''合计'' from tb group by fday'
exec(@sql) 

[dawugui]

--sql 2000

create table tmp
(
  fday varchar(2),
  fitem varchar(30),
  fph varchar(10),
  fbc varchar(20),
  fqty decimal(18,4)  
)

insert into tmp
select '01','S816','第七批','乙班',100
insert into tmp
select '02','S816','第七批','甲班',600
insert into tmp
select '02','S816','第七批','乙班',850
insert into tmp
select '03','S816','第七批','甲班',650  
go

declare @sql varchar(8000)
set @sql = 'select fday,fitem '
select @sql = @sql + ' , max(case when fph  = ''' + fph + ''' and fbc = ''' + fbc + '''  then fqty else 0 end) ' + fph + '_' + fbc 
from (select distinct fph,fbc  from tmp) as a
set @sql = @sql + ' ,sum(fqty) 合计 from tmp group by fday,fitem '
exec(@sql) 


drop table tmp

/*
fday fitem                          第七批_甲班               第七批_乙班               合计                                       
---- ------------------------------ -------------------- -------------------- ---------------------------------------- 
01   S816                           .0000                100.0000             100.0000
02   S816                           600.0000             850.0000             1450.0000
03   S816                           650.0000             .0000                650.0000
*/

[dawugui]

create table tmp
(
  fday varchar(2),
  fitem varchar(30),
  fph varchar(10),
  fbc varchar(20),
  fqty decimal(18,4)  
)

insert into tmp
select '01','S816','第七批','乙班',100
insert into tmp
select '02','S816','第七批','甲班',600
insert into tmp
select '02','S816','第七批','乙班',850
insert into tmp
select '03','S816','第七批','甲班',650  
go

declare @sql varchar(8000)
set @sql = 'select fday '
select @sql = @sql + ' , max(case when fitem = ''' + fitem + ''' and fph  = ''' + fph + ''' and fbc = ''' + fbc + '''  then fqty else 0 end) [' + fitem + '|' + fph + '|' + fbc + ']'
from (select distinct fitem,fph,fbc  from tmp) as a
set @sql = @sql + ' ,sum(fqty) 合计 from tmp group by fday '
exec(@sql) 


drop table tmp

/*
fday S816|第七批|甲班          S816|第七批|乙班          合计                                       
---- -------------------- -------------------- ---------------------------------------- 
01   .0000                100.0000             100.0000
02   600.0000             850.0000             1450.0000
03   650.0000             .0000                650.0000
*/

[-晴天]

没有别的办法,只能这样处理的.

[damo_baby]

行列转换。可以到我空间看看。

[loen113]

谢谢各位的回答，但如果我想扩展下，这方法就不行了，比如我加一个货号。
fday S813|第七批|甲班 S816|第七批|甲班 S816|第七批|乙班 合计
---- ----------------------------------------------------------- ---------------------------------------- ----------------------------------------
01 .0000 .0000 100.0000 100.0000
02 .0000 600.0000 850.0000 1450.0000
03 650.0000 650.0000 .0000 1300.0000
这种结果就错了。如果想知道每天的货号批次的合计数呢。正确的结果应该如下

fday S813|第七批|甲班 合计 S816|第七批|甲班 S816|第七批|乙班 合计
---- ----------------------------------------------------------- --------------------------
01 .0000 0 .0000 100.0000 100.0000
02 .0000 0 600.0000 850.0000 1450.0000
03 650.0000 650 650.0000 .0000 1300.0000