oracle 多列转成一列(列转行)、 行转列

1. 多列转成一列(列转行) -- 6列转成两列(列转行) 这就是最常见的列转行，主要原理是利用SQL里面的union with temp as (select a.iid_sn, a.product_name, a.sales_figures, a.selling_cost, a.pretax_profit, a.closing_inventory from is_import_detail a,

1.多列转成一列(列转行)

 

--6列转成两列(列转行)

这就是最常见的列转行，主要原理是利用SQL里面的union

with temp as
 (select
   a.iid_sn,
   a.product_name,
   a.sales_figures,
   a.selling_cost,
   a.pretax_profit,
   a.closing_inventory
    from is_import_detail a, is_import b
   where a.isi_sn = b.isi_sn
   and b.import_year=?
   and b.import_month=?
   and a.product_name=?)

--sql中要想实现特定的排序，可以适当加一些整数
select 1,'销售额' as salename, sales_figures as sale
  from temp
union
select 2,'销售成本' as salename, selling_cost as sale
  from temp
union
select 3,'税前利润' as salename, pretax_profit as sale
  from temp
union
select 5, '期末库存量' as serialname, closing_inventory as serial
  from temp

 

 

2.行转列

 

主要原理是利用decode函数、聚集函数(sum)，结合group by分组实现的，具体的sql如下：

select t.user_name,
       sum(decode(t.course, '语文', score, null)) as chinese,
       sum(decode(t.course, '数学', score, null)) as math,
       sum(decode(t.course, '英语', score, null)) as english
  from test_tb_grade t
 group by t.user_name
 order by t.user_name