将筛选的多个结果，以一条记录的形式放入另一个库。（附源码，求优化）

[[̲̅孤̲̅单̲̅一̲̅吻̲̅]]

原表：FeeItem（钱Debit，时间HotelDate，编号TransCode）
目标表：transcodeamount（hoteldate,T1,T2,T3,T4,T5,T6）
要求：从FeeItem表中统计某天（HotelDate）编号为100-105钱的总和，再插入transcodeamount表。

Declare @hoteldate datetime
Declare @T1 money
Declare @T2 money
Declare @T3 money
Declare @T4 money
Declare @T5 money
Declare @T6 money

SET @hoteldate='2008-07-06'

select @T1=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=100
select @T2=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=101
select @T3=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=102
select @T4=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=103
select @T5=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=104
select @T6=sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=105

Print CAST ( @T1 AS VARCHAR(10) )
Print CAST ( @T2 AS VARCHAR(10) )
Print CAST ( @T3 AS VARCHAR(10) )
Print CAST ( @T4 AS VARCHAR(10) )
Print CAST ( @T5 AS VARCHAR(10) )
Print CAST ( @T6 AS VARCHAR(10) )

insert into transcodeamount VALUES(@hoteldate,@T1,@T2,@T3,@T4,@T5,@T6)

[dawugui]

SET @hoteldate='2008-07-06'

insert into transcodeamount VALUES(@hoteldate,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=100,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=101,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=102,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=103,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=104,
select sum(Debit) from FeeItem WHERE HotelDate=@hoteldate and TransCode=105
)

[律己修心]

--如果有参数嗅探问题
SET @hoteldate='2008-07-06' 

exec(
'insert into transcodeamount VALUES( '+@hoteldate+'  , 
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+'  and TransCode=100, 
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+'  and TransCode=101, 
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+'  and TransCode=102, 
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+'  and TransCode=103, 
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+'  and TransCode=104, 
select sum(Debit) from FeeItem WHERE HotelDate= '+@hoteldate+'  and TransCode=105 
') 

[Andy-W]

Declare @hoteldate datetime

SET @hoteldate='2008-07-06'

insert into transcodeamount
Select 
	Max(Case TransCode When 100 Then T Else 0 End) As T1,
	Max(Case TransCode When 101 Then T Else 0 End) As T2,
	Max(Case TransCode When 102 Then T Else 0 End) As T3,
	Max(Case TransCode When 103 Then T Else 0 End) As T4,
	Max(Case TransCode When 104 Then T Else 0 End) As T5,
	Max(Case TransCode When 105 Then T Else 0 End) As T6
From 
(Select TransCode,sum(Debit) As T
	From FeeItem
	Where HotelDate=@hoteldate
		And TransCode Between 100 And 105
	Group By TransCode
) As a

[[̲̅孤̲̅单̲̅一̲̅吻̲̅]]

谢楼上三位

[[̲̅孤̲̅单̲̅一̲̅吻̲̅]]

第三个朋友的高明些
我加分结了