求SQL(100分)

我有两个表：
表1:（共4个车道
月、日、车道、客车、货车、摩托车
存储了在某时间，某车道通过的客车、货车、摩托车的数量

表2
月、日、车道1、车道2、车道3、车道4
存储了在某时间，车道1~车道4各通过的各种车辆数和。

比如表1数据（1月1日数据）
1，1,1,10,32,20 10+32+20=62
1，1,2,30,45,2 30+45+2=77
1，1,3,15,24,4 15+24+4=53
1，1,4,16,24,3 16+24+3=43
.....


表2应该为：
1，1，62，77，53，44
.....
求SQL语句，速度快、MSSQL

SELECT 表1.月, 表1.日,
Sum(IIf([车道]=1,[客车]+[货车]+[摩托车],0)) AS s1,
Sum(IIf([车道]=2,[客车]+[货车]+[摩托车],0)) AS s2,
Sum(IIf([车道]=3,[客车]+[货车]+[摩托车],0)) AS s3,
Sum(IIf([车道]=4,[客车]+[货车]+[摩托车],0)) AS s4
FROM 表1
GROUP BY 表1.月, 表1.日;

SELECT 表1.月, 表1.日,
Sum(IIf([车道]=1,[客车]+[货车]+[摩托车],0)) AS s1,
Sum(IIf([车道]=2,[客车]+[货车]+[摩托车],0)) AS s2,
Sum(IIf([车道]=3,[客车]+[货车]+[摩托车],0)) AS s3,
Sum(IIf([车道]=4,[客车]+[货车]+[摩托车],0)) AS s4
INTO 表2
FROM 表1
GROUP BY 表1.月, 表1.日;

SQL SERVER好像没有IIF函数,用联合查询:

SELECT 表1.月, 表1.日, Sum([客车]+[货车]+[摩托车]) AS s1,
Sum(0) AS s2, Sum(0) AS s3, Sum(0) AS s4
FROM 表1
WHERE (((表1.车道)=1))
GROUP BY 表1.月, 表1.日 UNION

SELECT 表1.月, 表1.日, Sum(0) AS s1,
Sum([客车]+[货车]+[摩托车]) AS s2, Sum(0) AS s3, Sum(0) AS s4
FROM 表1
WHERE (((表1.车道)=2))
GROUP BY 表1.月, 表1.日 UNION

SELECT 表1.月, 表1.日, Sum(0) AS s1,Sum(0) AS s2,
Sum([客车]+[货车]+[摩托车]) AS s3, Sum(0) AS s4
FROM 表1
WHERE (((表1.车道)=3))
GROUP BY 表1.月, 表1.日 UNION

SELECT 表1.月, 表1.日, Sum(0) AS s1,Sum(0) AS s2,Sum(0) AS s3,
Sum([客车]+[货车]+[摩托车]) AS s4
FROM 表1
WHERE (((表1.车道)=4))
GROUP BY 表1.月, 表1.日

g622?

用游标会满一些,是否可以接受?

写了一个存储过程如下:

CREATE PROCEDURE gxg AS
SELECT 月, 日, 客车 + 货车 + 摩托车 AS s1, 0 AS s2, 0 AS s3, 0 AS s4
INTO #temp1
FROM 表1
WHERE 车道 = 1
UNION
SELECT 月, 日, 0 AS s1, 客车 + 货车 + 摩托车 AS s2, 0 AS s3, 0 AS s4
FROM 表1
WHERE 车道 = 2
UNION
SELECT 月, 日, 0 AS s1, 0 AS s2, 客车 + 货车 + 摩托车 AS s3, 0 AS s4
FROM 表1
WHERE 车道 = 1
UNION
SELECT 月, 日, 0 AS s1, 0 AS s2, 0 AS s3, 客车 + 货车 + 摩托车 AS s4
FROM 表1
WHERE 车道 = 4;

select 月, 日, sum(s1) AS s1, sum(s2) AS s2, sum(s3) AS s3,sum(s4) AS s4 into 表2
from #temp1
group By 月, 日;

运行此存储过程可生成表2

多谢gxg8816热心帮忙。

多人接受答案了。