简单行列转换探索

今天在ITEYE上看到有关如下需求的一个帖子（http://oracle.group.iteye.com/group/topic/20883）：
“现我有一个talbe  Material 
它的列位有Name,type, state,  type有A,B,C 它们都有三种state 有new used drop 分别用 100，200，300代表 比如 数据库中有以下数据 

       Name     Type    State 
------------------------------------ 
      M1        A       100 
      M1        B       200 
      M1        C       300 
      M2        A       200 
      M2        B       100 
      M3        A       100 
---------------------------------- 
现在我要查询出来的结果是 一个Name 就有且仅有一条记录 该记录包括它的所有type 的 状态  如： 
       Name   AState  BState  CState 
--------------------------------------- 
       M1     New     Used     Drop 
       M2     Used    New 
       M3     New       
--------------------------------------- 
material 是name 与type 确定唯一 
这个sql各位是怎么写呀？ 谢谢 ”
想到其实这种行列转换的需求虽说不常有，但偶尔也会碰到，于是稍微研究了一下，发现原帖回复中的方法基本都是错的 
虽然其中的几个帖子也能查出结果，但是如果发生state值有重复的情况，查询结果就会出错，于是我想了个语句出来，现贴出来，请指教下：

select distinct a1.name,a1.astate,b1.bstate,c1.cstate from 

       (select a.name,

               (case (select b.state from tests b where b.name=a.name and type ='A')

               when 100 then 'New' 

               when 200 then 'Used'

               when 300 then 'Drop'  

                 end) Astate

      from tests a) a1 left join

      (select b.name,

              (case (select c.state from tests c where c.name=b.name and type ='B')

              when 100 then 'New' 

              when 200 then 'Used'

              when 300 then 'Drop'  

               end) Bstate

        from tests b) b1 on a1.name=b1.name

        left join 

        (select c.name,

        (case (select d.state from tests d where d.name=c.name and type ='C')

              when 100 then 'New' 

              when 200 then 'Used'

               when 300 then 'Drop'  

               end) Cstate

from tests c) c1 on a1.name=c1.name

​原设想是将上述语句中子查询里的case语句来查做成state列的但是发现，会提示你找不到FROM关键字，这个不知道是不是因为重命名后的关键字只能用一次导致的……得研究下
这个语句估计效率会有点低……求指教