oracle 11g行转列 列转行

行转列：

SELECT *   
FROM   src_table   
UNPIVOT (param_value FOR param_name IN (product_color AS 'product_color', product_type AS 'product_type', is_intelligent AS 'is_intelligent'));

 

列转换：

 select *  from (

select flow,xx from tab

)   a pivot (max(value) for enname in ( 'flow' flow ,'xx')) b order by id ";

 

 

我自己的测试

注意：建表插入的时候varchar字符型必须要加上'',

 insert into s1 values('yuan','english',80);

 

SELECT  * FROM  s1 PIVOT ( MAX (score) for  subject IN ( 'chinese'   chinese , 'math'   math  , 'english'   english ) )

注意这里max的是分数，in的是subject　　

 返回来

 

  SELECT * FROM 
   ( SELECT * FROM s1
    PIVOT ( MAX(score) for subject IN( 'chinese'  chinese , 'math'  math  , 'english'  english ) ) )
 UNPIVOT ( score FOR subject IN (   chinese ,   math  ,   english ) ) 

 

注意这里 最后一句的in chinese 之类，chinese不需要加''

转载于:https://www.cnblogs.com/lj821022/p/5029208.html