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,

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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！