test1_for_decode.txt

oracle中重要的常用函数decode.以及自定义函数

请根据A表的内容用一句SQL生成报表A.
表A：
Order_no 	amt 		item		item_group
001		2000.00	HardDisk	Hardware
001		3000.00 	17'Display	Hardware
002		1600.00 	Windows2000	Software
002		800.00 	Documentation	Other

报表A:
Order_no 	Hardware 	Software 		Other
001		5000.00		0		0
002		0.00	  	1600.00		800


答案:

select a.order_no,
       sum(decode(a.item_group, 'Hardware', amt, 0)) hardware,
       sum(decode(a.item_group, 'Software', amt, 0)) software,
       sum(decode(a.item_group, 'Other', amt, 0)) other
  from a
 group by order_no