Transform语句再查询

visualPaul

数据集

Name	Date1	Amount
B	2012/4/1	200
C	2012/5/17	100
C	2012/5/23	100
B	2012/1/8	100
C	2012/2/29	200
C	2012/2/25	100
A	2012/5/12	100
B	2012/3/24	100
A	2012/4/9	200
C	2012/5/3	100

结果集

Name	Total	1	2	3	4	5
A	300				200	100
B	400	100		100	200
C	600		300			300
[code=sql]transform  sum(Amount) select Name, sum(Amount) as Total from [Data$] group by Name  pivot month(Date1)[/code]
由于不能对transform再查询，也不能将transform结果集  与 union 联合使用，有何聪明办法实现下表结果呢
Name	Total	1	2	3	4	5
A	300				200	100
B	400	100		100	200
C	600		300			300
Total	1300	100	300	100	400	400

详见附件 请置于D盘根目录

visualPaul

用笨办法搞定了，有聪明办法吗？
btw 我使用的是Excel 2010。 Excel 2010里面 GROUP BY后可使用变量了？ 我正纳闷呢，但确实可以哦

1. transform sum(Amount) select Name, sum(Amount) as Total from (select * from [Data$] union all  (select "Total", dateserial(year(Date1),month(Date1),1), sum(Amount) from [Data$] group by dateserial(year(Date1),month(Date1),1))) group by Name pivot month(Date1)

复制代码

visualPaul

更多层级的分类汇总，类似于数据透视表
数据集：

Name	Location	Product	Date1	Amount
A	SH	x2	2012/5/7	200
A	GZ	x3	2012/4/1	200
C	GZ	x1	2012/3/20	200
A	SH	x2	2012/2/5	100
B	GZ	x1	2012/3/9	100
B	GZ	x1	2012/3/19	200
C	SH	x2	2012/3/21	100
C	BJ	x2	2012/3/31	200
B	SH	x1	2012/3/6	100
A	BJ	x2	2012/2/13	100
A	SH	x2	2012/1/15	100
B	GZ	x3	2012/5/16	200
C	SH	x1	2012/5/2	200
C	BJ	x2	2012/5/23	200
A	BJ	x2	2012/5/18	100
A	GZ	x2	2012/5/22	200
C	GZ	x1	2012/2/12	200
C	GZ	x1	2012/5/31	200
C	SH	x1	2012/5/30	200
C	GZ	x3	2012/1/4	100

结果集：

Name	Location	Product	Total	2012-01	2012-02	2012-03	2012-04	2012-05
A			1000	100	200		200	500
A	BJ		200		100			100
A	BJ	x2	200		100			100
A	GZ		400				200	200
A	GZ	x2	200					200
A	GZ	x3	200				200
A	SH		400	100	100			200
A	SH	x2	400	100	100			200
B			600			400		200
B	GZ		500			300		200
B	GZ	x1	300			300
B	GZ	x3	200					200
B	SH		100			100
B	SH	x1	100			100
C			1600	100	200	500		800
C	BJ		400			200		200
C	BJ	x2	400			200		200
C	GZ		700	100	200	200		200
C	GZ	x1	600		200	200		200
C	GZ	x3	100	100
C	SH		500			100		400
C	SH	x1	400					400
C	SH	x2	100			100
Total			3200	200	400	900	200	1500

见新附件

代码：
[code=sql]transform sum(Amount) select Name, Location, Product, sum(Amount) as Total from ( select * from [Data$]  union all (select Name, Location, "", dateserial(year(date1),month(Date1),1), sum(Amount) from [Data$] group by Name, Location, dateserial(year(date1),month(Date1),1)) union all (select Name, "", "", dateserial(year(date1),month(Date1),1), sum(Amount) from [Data$] group by Name, dateserial(year(date1),month(Date1),1)) union all (select "Total", "", "", dateserial(year(date1),month(Date1),1), sum(Amount) from [Data$] group by dateserial(year(date1),month(Date1),1) ) ) group by Name, Location, Product pivot format(Date1, "yyyy-mm")[/code]

gjj136138139

顶起，太厉害了，这么多层的分类汇总竟然就这样实现了。