求SQL语句

[wjfxyj]

求SQL语句
我有一表

id rq pm sl je pj
1 2012-02-01 铅笔 100 500.00 5.00
2 2012-02-01 钢笔 50 1000.00 20.00
3 2012-02-01 笔记本 60 600.00 10.00
4 2012-02-02 铅笔 80 480.00 6.00
5 2012-02-02 钢笔 70 2100.00 30.00
6 2012-02-02 笔记本 60 1200.00 20.00
7 2012-02-03 铅笔 50 450.00 9.00
8 2012-02-03 钢笔 90 3600.00 40.00
9 2012-02-03 笔记本 60 1800.00 30.00

直至2012-02-29，每天都有数据

我要得到另一表

id rq 总sl 总je 总pj 铅笔sl 钢笔sl 笔记本sl 铅笔je 钢笔je 笔记本je
1 2012-02-0 210 2100.00 10.00 100 50 60 500.00 1000.00 600.00
2 2012-02-0 210 3780.00 23.30 80 70 60 480.00 2100.00 1200.00
3 2012-02-03 200 4850.00 292.50 50 90 60 450.00 3600.00 1800.00

合计 620 10730.00 230 210 180 1430.00 6700.00 3600.00

求SQL语句

[wjfxyj]

我用的是SQL SERVER 2000


ROW_NUMBER() 函数不能识别

[wjfxyj]

上表有错误

id rq 总sl 总je 总pj 铅笔sl 钢笔sl 笔记本sl 铅笔je 钢笔je 笔记本je
1 2012-02-01 210 2100.00 10.00 100 50 60 500.00 1000.00 600.00
2 2012-02-02 210 3780.00 23.30 80 70 60 480.00 2100.00 1200.00
3 2012-02-03 200 4850.00 292.50 50 90 60 450.00 3600.00 1800.00

合计 620 10730.00 230 210 180 1430.00 6700.00 3600.00

[soft_lugy]

--id rq pm sl je pj 测试数据
--1 2012-02-01 铅笔 100 500.00 5.00
--2 2012-02-01 钢笔 50 1000.00 20.00
--3 2012-02-01 笔记本 60 600.00 10.00
--4 2012-02-02 铅笔 80 480.00 6.00
--5 2012-02-02 钢笔 70 2100.00 30.00
--6 2012-02-02 笔记本 60 1200.00 20.00
--7 2012-02-03 铅笔 50 450.00 9.00
--8 2012-02-03 钢笔 90 3600.00 40.00
--9 2012-02-03 笔记本 60 1800.00 30.00

if OBJECT_ID('[Table1]') is not null drop table [Table1]
create table [Table1](id int,rq datetime,pm nvarchar(50),sl decimal(15,2),je decimal(15,2),pj decimal(15,2))

insert into [Table1]
select 1, '2012-02-01','铅笔',100,500.00,5.00 union all
select 2, '2012-02-01','钢笔',50,1000.00,20.00 union all
select 3, '2012-02-01','笔记本', 60, 600.00, 10.00 union all
select 4, '2012-02-02', '铅笔', 80, 480.00, 6.00 union all
select 5, '2012-02-02', '钢笔', 70, 2100.00, 30.00 union all
select 6, '2012-02-02', '笔记本',60, 1200.00, 20.00 union all
select 7, '2012-02-03', '铅笔', 50, 450.00, 9.00 union all
select 8, '2012-02-03', '钢笔', 90, 3600.00, 40.00 union all
select 9, '2012-02-03', '笔记本',60, 1800.00, 30.00

select ROW_NUMBER() over(order by rq) as id,rq,SUM(sl) as 总sl,SUM(je) as 总je,SUM(pj) as 总pj,
sum(case when pm='铅笔' then sl else 0 end) as 铅笔sl,
sum(case when pm='钢笔' then sl else 0 end) as 钢笔sl,
sum(case when pm='笔记本' then sl else 0 end) as 笔记本sl,
sum(case when pm='铅笔' then je else 0 end) as 铅笔je,
sum(case when pm='钢笔' then je else 0 end) as 钢笔je,
sum(case when pm='笔记本' then je else 0 end) as 笔记本je,
sum(case when pm='铅笔' then pj else 0 end) as 铅笔pj,
sum(case when pm='钢笔' then pj else 0 end) as 钢笔pj,
sum(case when pm='笔记本' then pj else 0 end) as 笔记本pj
from [Table1] 
group by rq

drop table [Table1]

--id	rq	总sl	总je	总pj	铅笔sl	钢笔sl	笔记本sl	铅笔je	钢笔je	笔记本je	铅笔pj	钢笔pj	笔记本pj
--1	2012-02-01 00:00:00.000	210.00	2100.00	35.00	100.00	50.00	60.00	500.00	1000.00	600.00	5.00	20.00	10.00
--2	2012-02-02 00:00:00.000	210.00	3780.00	56.00	80.00	70.00	60.00	480.00	2100.00	1200.00	6.00	30.00	20.00
--3	2012-02-03 00:00:00.000	200.00	5850.00	79.00	50.00	90.00	60.00	450.00	3600.00	1800.00	9.00	40.00	30.00

[ixkixkix]

交叉表，