面试失败，这道SQL难住了我。
存表格式：
data表
id revtime(数据返回时间）channel(通道号) val(值)
1 2007-5-1 1 79
1 2007-5-1 2 46
1 2007-5-1 3 265
1 2007-5-2 1 80
1 2007-5-2 2 40
1 2007-5-2 3 266
channel表
id channel
1 1
1 2
1 3
请用SQL操作，最终显示为：
id revtime channel1data channel2data channel3data ...//根据channel里相同id对应的通道数显示出对应通道的数据，例如：id为1有1\2\3三个通道，故应该显示channel1\channel2\channel3三列，以次类推。
1 2007-5-1 79 46 265
1 2007-5-2 80 40 266
大家给点建议，该怎么做？
[解决办法]
select id,revtime,
max(case channel when 1 then val end) as channel1data,
max(case channel when 2 then val end) as channel2data,
max(case channel when 3 then val end) as channel3data
from [data]
group by id,revtime
[解决办法]
select id,revtime,
max(case when channel=1 then val else 0 end ) as channel1data,
max(case when channel=2 then val else 0 end ) as channel2data,
max(case when channel=3 then val else 0 end ) as channel3data
from data表
group by id,revtime
------解决方案--------------------
select id,revtime,
sum(case when channel=1 then val else 0 end ) as channel1 ,
sum(case when channel=2 then val else 0 end ) as channel2 ,
sum(case when channel=3 then val else 0 end ) as channel3
from data表
group by id,revtime
[解决办法]
select id,revtime ,sum(case when channel=1 then val end)as date1,
sum(case when channel=2 then val end)as date2,
sum(case when channel=3 then val end)as date3
from t
group by id,revtime
id revtime date1 date2 date3
------ ---------- ----------- ----------- -----------
1 2007-5-1 79 46 265
1 2007-5-2 80 40 266
(2 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
