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

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