求助oracle行列数据转换问题

[alick723]

现在有如下表格，表格里每个城市都只有3行数据，前3个商品的销售信息：

城市 商品 金额 销售前3名
无锡 米 100 1
无锡 油 400 2
无锡 青菜 300 3
南京 大豆 500 1
南京 黄瓜 300 2
南京 西红柿 600 3
.....

要得到如下结果（一个城市一行数据）：

城市 商品第1名名称、商品第1名金额、商品第2名名称、商品第2名金额、商品第3名名称、商品第3名金额
无锡 米 100 油 400 青菜 300
南京 大豆 500 黄瓜 300 西红柿 600
.....

[stelf]

with tmp as ( select '无锡' as p_City, '米' as p_goods, '100' as p_amt, '1' as p_order from dual union all select '无锡' as p_City, '油' as p_goods, '400' as p_amt, '2' as p_order from dual union all select '无锡' as p_City, '青菜' as p_goods, '300' as p_amt, '3' as p_order from dual union all select '南京' as p_City, '大豆' as p_goods, '500' as p_amt, '1' as p_order from dual union all select '南京' as p_City, '黄瓜' as p_goods, '300' as p_amt, '2' as p_order from dual union all select '南京' as p_City, '西红柿' as p_goods, '600' as p_amt, '3' as p_order from dual) select tmp.p_city, max(case when tmp.p_order = 1 then tmp.p_goods else null end), max(case when tmp.p_order = 1 then tmp.p_amt else null end), max(case when tmp.p_order = 2 then tmp.p_goods else null end), max(case when tmp.p_order = 2 then tmp.p_amt else null end), max(case when tmp.p_order = 3 then tmp.p_goods else null end), max(case when tmp.p_order = 3 then tmp.p_amt else null end) from tmp group by tmp.p_city;