oracle列转行 多个字段_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, 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