oracle 我想在PL/SQL中将数据从行转换为列,那么,有人能帮助我吗?

aurhwmvo  于 2022-12-03  发布在  Oracle
关注(0)|答案(1)|浏览(188)

我的输入数据是:

Vechical_NUM  Vehical_name Period Year cost
1111          Car          Aug     2022 $5000
1111          Car          Sep     2022 $5100
1111          Car          Oct     2022 $5300
1111          Car          Nov     2022 $5000
1111          Car          Dec     2022 $5000
1111          Car          Jan     2023 $5000
1111          Car          Feb     2023 $5000
:              :            :       :    :
:              :            :       :    :
1111          Car          Aug     2023 $10000
1111          Car          sep     2023 $10000
1111          Car          Oct     2023 $10000

1111          Car          Nov     2023 $10000

1111          Car          Dec     2023 $10000
2222          Truck        Aug     2022 $10000
2222          Truck        Sep     2022 $10000
2222          Truck        Oct     2022 $10000
2222          Truck        Nov     2022 $10000

2222          Truck        Dec     2022 $10000

2222          Truck        Jan     2023 $10000
2222          Truck        Feb     2023 $10000
2222          Truck        Mar     2023 $10000
:              :            :      :     :
2222          Truck        Dec     2023 $5000

预期输出

Vechical_NUM  Vehical_name  Aug_2022 Sep_2022 Oct_2022 ....... Dec-2023 
1111           Car           $5000    $5100  $5300    .......  $5000
2222           Truck         $10000   $10000 $10000    -----   $10000
wfveoks0

wfveoks01#

使用PIVOT

SELECT *
FROM   table_name
PIVOT (
  SUM(cost) FOR (period, year) IN (
    ('Aug', 2022) AS aug_2022,
    ('Sep', 2022) AS sep_2022,
    ('Oct', 2022) AS oct_2022,
    ('Nov', 2022) AS nov_2022,
    ('Dec', 2022) AS dec_2022,
    ('Jan', 2023) AS jan_2023,
    ('Feb', 2023) AS feb_2023,
    -- ...
    ('Dec', 2023) AS dec_2023
  )
)

其中,对于示例数据:

CREATE TABLE table_name (Vechical_NUM, Vehical_name, Period, Year, cost) AS
SELECT 1111, 'Car',   'Aug', 2022,  5000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Sep', 2022,  5100 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Oct', 2022,  5300 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Nov', 2022,  5000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Dec', 2022,  5000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Jan', 2023,  5000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Feb', 2023,  5000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Aug', 2023, 10000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'sep', 2023, 10000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Oct', 2023, 10000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Nov', 2023, 10000 FROM DUAL UNION ALL
SELECT 1111, 'Car',   'Dec', 2023, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Aug', 2022, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Sep', 2022, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Oct', 2022, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Nov', 2022, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Dec', 2022, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Jan', 2023, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Feb', 2023, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Mar', 2023, 10000 FROM DUAL UNION ALL
SELECT 2222, 'Truck', 'Dec', 2023,  5000 FROM DUAL;

输出:
| 向量_编号|车辆名称|2022年8月|2022年9月|2022年10月|二〇二二年十一月|二〇二二年十二月|2023年1月|2023年2月|二〇二三年十二月|
| - -|- -|- -|- -|- -|- -|- -|- -|- -|- -|
| 小行星1111|汽车|五千|小行星5100|小行星5300|五千|五千|五千|五千|万|
| 小行星2222|卡车|万|万|万|万|万|万|万|五千|
fiddle

相关问题