如何实现“多层”交叉报表,即按多个字段交叉查询。
如何实现“多层”交叉报表,即按多个字段交叉查询。例如:
编号 地点 月份 价格
---- ---- ----------- -----------
A002 国内 10 200
A005 国内 10 500
A001 国外 10 100
A003 国外 10 300
A004 国外 10 400
A002 国内 11 200
A005 国内 11 500
A003 国外 11 300
A004 国外 11 400
希望能按
[编号] [国外_10月份], [国外_11月份],[国内_10月份],[国内_11月份]
的形式交叉统计 [价格],
即按照 [地点] 和 [月份] 多层交叉统计商品销售额。
测试数据如下:
Select 'A001' As [编号], '国外' As [地点], 10 As [月份], 100 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 10 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 10 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 10 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 10 As [月份], 500 As [价格]
Union
Select 'A002' As [编号], '国内' As [地点], 11 As [月份], 200 As [价格]
Union
Select 'A003' As [编号], '国外' As [地点], 11 As [月份], 300 As [价格]
Union
Select 'A004' As [编号], '国外' As [地点], 11 As [月份], 400 As [价格]
Union
Select 'A005' As [编号], '国内' As [地点], 11 As [月份], 500 As [价格]
Order By [月份],[地点],[编号]