oracle多列轉(zhuǎn)成一列(列轉(zhuǎn)行)、行轉(zhuǎn)列

1. 多列轉(zhuǎn)成一列(列轉(zhuǎn)行) -- 6列轉(zhuǎn)成兩列(列轉(zhuǎn)行) 這就是最常見的列轉(zhuǎn)行，主要原理是利用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.多列轉(zhuǎn)成一列(列轉(zhuǎn)行)

--6列轉(zhuǎn)成兩列(列轉(zhuǎn)行)

這就是最常見的列轉(zhuǎn)行，主要原理是利用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中要想實現(xiàn)特定的排序，可以適當(dāng)加一些整數(shù)
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.行轉(zhuǎn)列

主要原理是利用decode函數(shù)、聚集函數(shù)(sum)，結(jié)合group by分組實現(xiàn)的，具體的sql如下：

select t.user_name,
sum(decode(t.course, '語文', score, null)) as chinese,
sum(decode(t.course, '數(shù)學(xué)', 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

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com