sql语句问题(100分)

源表 Consumedata

Invoice_NO Depart_Code Tatal_Amount
M0041527 AAA 1000
M0041527 DDD 100
M0041527 XXX 300
M0042527 AAA 100
M0041527 AAA 2000

通过一SQL语句得到如下结果:

Invoice_No AAA DDD XXX

M0041527 3000 100 300
M0042527 100 0 0


环境elphi6+SQl Server 2000

我写的sql语句:
select Invoice_no ,sum(iif(depart_Code="AAA",Total_Amount,0)) as "AAA" ,
sum(iif(depart_Code="DDD",Total_Amount,0)) as "DDD"
From ConsumeData Group By Invoice_no

但运行时报语法错:等号=附近

用case吧

select Invoice_NO ,
sum( Case Depart_Code When 'AAA' then Tatal_Amount else 0 end) aaa,
sum( Case Depart_Code When 'ddd' then Tatal_Amount else 0 end) ddd ,
sum( Case Depart_Code When 'xxx' then Tatal_Amount else 0 end) xxx
from Consumedata
group by Invoice_NO

剛為一個富翁寫的，你參考一下，看有無幫助：
数据如下： Table1 (表名)
Code_No, Type, Money (字段名)
1、西瓜、现金、0.5
2、西瓜、IC卡、1.5
3、黄瓜、现金、1.0
4、黄瓜、IC卡、2.0
5、木瓜、现金、2.5
6、木瓜、IC卡、3.0
现在要制做如下格式的报表：
西瓜、黄瓜、木瓜
现金 0.5 1.0 2.5
IC卡 1.5 2.0 3.0
如果还有其它商品类型的话，列头还要动态增加

過程如下：

select Code_No,Type,Sum(Money) as Total_Scalar
into #T
from Table1 Group by Code_No,Type

Create #Temptab(Add_ID int IDENTITY (1, 1) NOT NULL,Type varchar(50) Null) --返回表
insert into #Temptab(Type) select Type from #T

declare @Code_No varchar(50),@Type varchar(50),@Total_Scalar float
declare #MyCur cursor For Select Code_No,Type,Total_Scalar from #T
Open #MyCur
Fetch Next from #MyCur into @Code_No,@Type,@Total_Scalar
while @@fetch_status=0
begin
if not ( Exists(select Name from Syscolumns where Id in(select ID from Sysobjects where Name='#Temptab') and Name='A'+@Code_No) )
Exec(' Alter Table #Temptab add A'+@Code_No+' varchar(50) Null')
Exec(' Update #Temptab set A'+@Code_No+'='+@Total_Scalar+' where Type='+''''+@Type+'''')
Fetch Next from #MyCur into @Code_No,@Type,@Total_Scalar
end
Close #MyCur
Deallocate #MyCur

select * from #Temptab
Drop table #T
Drop table #Temptab

返回了如#Temptab結構的表結構是否你想要的？

感谢各位