在线等，比较复杂的问题，得出结果马上结贴

[zuoxingyu]

我的表如下
date1 dealFlag stimes smoneys
1 1 1 2.5
1 2 1 3
1 3 2 5
2 2 1 2.5
2 3 3 10
数据基本如上，其中date1是日期，每个月的哪天，dealfalg是早，中，晚餐，stimes是消费次数，smoneys是消费总金额
我现在想得到这样一个记录集
日期 早餐 次数 金额 中餐 次数 金额 晚餐 次数 金额
1 1 1 2.5 2 1 3 3 2 5
2 1 0 0 2 1 2.5 3 3 10

各位大侠帮忙，得出结果马上结贴

[wzy_love_sly]

都几点了 还等

[OracleRoob]

select
     date1 as 日期,
     1 as 早餐,
     sum(case when dealFlag = 1 then stimes else 0 end) a 早餐次数,
     sum(case when dealFlag = 1 then smoneys else 0 end) a 早餐金额,
     2 as 午餐,
     sum(case when dealFlag = 2 then stimes else 0 end) a 午餐次数,
     sum(case when dealFlag = 2 then smoneys else 0 end) a 午餐金额,
     3 as 晚餐,
     sum(case when dealFlag = 3 then stimes else 0 end) a 晚餐次数,
     sum(case when dealFlag = 3 then smoneys else 0 end) a 晚餐金额

from 表名
group by date1

[OracleRoob]

--改一点：

select
     date1 as 日期,
     1 as 早餐,
     sum(case when dealFlag = 1 then stimes else 0 end) 早餐次数,
     sum(case when dealFlag = 1 then smoneys else 0 end) 早餐金额,
     2 as 午餐,
     sum(case when dealFlag = 2 then stimes else 0 end) 午餐次数,
     sum(case when dealFlag = 2 then smoneys else 0 end) 午餐金额,
     3 as 晚餐,
     sum(case when dealFlag = 3 then stimes else 0 end) 晚餐次数,
     sum(case when dealFlag = 3 then smoneys else 0 end) 晚餐金额

from 表名
group by date1

[中国风]

create table T(date1 int,    dealFlag int,      stimes int,      smoneys numeric(18,2))
insert T select 1,               1,                         1,               2.5 
insert T select 1,               2,                         1,               3 
insert T select 1,               3,                         2,               5 
insert T select 2,               2,                         1,               2.5 
insert T select 2,               3,                         3,               10

go


select
	date1,
	[早餐]=1,
	[次数]=sum(case when dealFlag=1 then stimes else 0 end),
	[金额]=sum(case when dealFlag=1 then smoneys else 0 end),
	[中餐]=2,
	[次数]=sum(case when dealFlag=2 then stimes else 0 end),
	[金额]=sum(case when dealFlag=2 then smoneys else 0 end),
	[晚餐]=3,
	[次数]=sum(case when dealFlag=3 then stimes else 0 end),
	[金额]=sum(case when dealFlag=3 then smoneys else 0 end)
from 
	T
group by date1

date1       早餐          次数          金额                                       中餐          次数          金额                                       晚餐          次数          金额                                       
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- 
1           1           1           2.50                                     2           1           3.00                                     3           2           5.00
2           1           0           .00                                      2           1           2.50                                     3           3           10.00

（所影响的行数为 2 行）

[OracleRoob]

--或：

select
     date1 as 日期,
     1 as 早餐,
     sum(case when dealFlag = 1 then stimes else 0 end) as 早餐次数,
     sum(case when dealFlag = 1 then smoneys else 0 end) as 早餐金额,
     2 as 午餐,
     sum(case when dealFlag = 2 then stimes else 0 end) as 午餐次数,
     sum(case when dealFlag = 2 then smoneys else 0 end) as 午餐金额,
     3 as 晚餐,
     sum(case when dealFlag = 3 then stimes else 0 end) as 晚餐次数,
     sum(case when dealFlag = 3 then smoneys else 0 end) as 晚餐金额

from 表名
group by date1

[-狙击手-]

create table ta(date1 int,dealFlag int,stimes int,smoneys  numeric(12,2))
insert ta select 1,1,          1,2.5 
insert ta select 1,2,          1,3 
insert ta select 1,3,          2,5 
insert ta select 2,2,          1,2.5 
insert ta select 2,3,          3,10 

go

declare @s varchar(8000)
set @s = 'select date1'
select @s = @s + ',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+']= max(case when dealflag = '+ltrim(dealflag)+ ' then dealflag else Null end)'+
',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+'次数]= sum(case when dealflag = '+ltrim(dealflag)+ ' then stimes else 0 end)'+
',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+'金额 ]= sum(case when dealflag = '+ltrim(dealflag)+ ' then smoneys else 0 end)'
from (select distinct dealflag from ta) a
exec(@s+ ' from ta group by date1')


drop table ta
/*
date1       早           早次数         早金额                                      中           中次数         中金额                                      晚           晚次数         晚金额                                      
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- 
1           1           1           2.50                                     2           1           3.00                                     3           2           5.00
2           NULL        0           .00                                      2           1           2.50                                     3           3           10.00

警告: 聚合或其它 SET 操作消除了空值。

*/

[-狙击手-]

create table ta(date1 int,dealFlag int,stimes int,smoneys  numeric(12,2))
insert ta select 1,1,          1,2.5 
insert ta select 1,2,          1,3 
insert ta select 1,3,          2,5 
insert ta select 2,2,          1,2.5 
insert ta select 2,3,          3,10 

go

declare @s varchar(8000)
set @s = 'select date1'
select @s = @s + ',['+case dealflag when 1 then '早' when 2 then '中' when 3 then '晚' end+']= max(case when dealflag = '+ltrim(dealflag)+ ' then dealflag else Null end)'+
',[次数]= sum(case when dealflag = '+ltrim(dealflag)+ ' then stimes else 0 end)'+
',[金额]= sum(case when dealflag = '+ltrim(dealflag)+ ' then smoneys else 0 end)'
from (select distinct dealflag from ta) a
exec(@s+ ' from ta group by date1')


drop table ta
/*
date1       早           次数          金额                                       中           次数          金额                                       晚           次数          金额                                       
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- 
1           1           1           2.50                                     2           1           3.00                                     3           2           5.00
2           NULL        0           .00                                      2           1           2.50                                     3           3           10.00

警告: 聚合或其它 SET 操作消除了空值。


*/

[中国风]

--动态生成方法;

create table T(date1 int,    dealFlag int,      stimes int,      smoneys numeric(18,2))
insert T select 1,               1,                         1,               2.5 
insert T select 1,               2,                         1,               3 
insert T select 1,               3,                         2,               5 
insert T select 2,               2,                         1,               2.5 
insert T select 2,               3,                         3,               10

go

declare @s nvarchar(4000)
set @s='select date1'
select @s=@s+',['+case when dealFlag=1 then '早餐' when dealFlag=2 then '中餐' else '晚餐' end+']='+rtrim(dealFlag)+',[次数]='+'sum(case when dealFlag='+rtrim(dealFlag)+' then stimes else 0 end),'+
			'[金额]='+'sum(case when dealFlag='+rtrim(dealFlag)+' then smoneys else 0 end)'
from T
group by dealFlag

exec(@s+' from T group by date1')

date1       早餐          次数          金额                                       中餐          次数          金额                                       晚餐          次数          金额                                       
----------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- ----------- ----------- ---------------------------------------- 
1           1           1           2.50                                     2           1           3.00                                     3           2           5.00
2           1           0           .00                                      2           1           2.50                                     3           3           10.00

[wzy_love_sly]

create table a (date int,dealflag int,stimes int,smoneys decimal(10,1))
insert into a select 1,1,1,2.5
insert into a select 1,2,1,3
insert into a select 1,3,2,5
insert into a select 2,2,1,2.5
insert into a select 2,3,3,1
--其中date1是日期，每个月的哪天，dealfalg是早，中，晚餐，stimes是消费次数，smoneys是消费总金额 
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+case when dealflag=1 then '早餐' when dealFlag=2 then '中餐' when dealFlag=3 then  '晚餐' end +'='''+cast(dealFlag as varchar(10))+''''+
',sum(case when dealflag='''+cast(dealflag as varchar(10))+''' then 1 else 0 end) as ''次数'',
sum(case when dealflag='''+cast(dealflag as varchar(10))+''' then smoneys else 0 end) as ''金额'''
from a group by dealflag
print @sql
exec('select date'+@sql+' from a group by date')

[code=SQL]date	早餐	次数	金额	中餐	次数	金额	晚餐	次数	金额
1	1	1	2.5	2	1	3.0	3	1	5.0
2	1	0	0.0	2	1	2.5	3	1	1.0