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