我想在 PL/SQL 中将数据从行转换为列。那么，任何人都可以帮助我吗？

Question

我的输入数据是:

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

Answer 1

使用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;

输出:

DEC_2023100005000