oracle 列转行，行转列

 

复制表结构和数据

CREATE TABLE table_name AS SELECT * FROM old_table_name;

只复制表结构

CREATE TABLE table_name AS SELECT * FROM old_table_name WHERE 1=2

With as 临时表

With t as (select 1 id,2 pid from dual),

t2 as (select 1 id,22 pid from dual)

Select t2.* from t,t2 where t.id=t2.id

行转列查询

with t as (

Select 'w' name, 300 nums ,1 bb from dual

Union

Select 'q' name, 200 nums ,1 bb from dual

Union

Select 'e' name, 100 nums ,1 bb from dual

Union

Select 'w' name, 111 nums ,2 bb from dual

Union

Select 'e' name, 222 nums ,2 bb from dual

Union

Select 'q' name, 333 nums ,2 bb from dual

)select * from  t pivot (sum(nums) for name in ('w','e','q'))order by  bb

列转行查询

With t as (

Select '1' as  id,'桃子' as name ,'100'  as q1 ,'200' as  q2,'300' as q3,'400'  as q4 from dual

Union

Select '2' id,'苹果' as  name ,'111' q1,'222' q2,'333' q3, '444'  q4 from dual

Union

Select '3' id,'西瓜' as  name,'123' q1,'234' q2,'345' q3,'456' q4 from dual

) Select * from t unpivot(nums for pp in (q1,q2,q3,q4)) order by id ;