I'd like to convert my data from rows to columns in PL/SQL. So, can anyone assist me with this?

Question

My Input data is :

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

Expected Out put

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

Welcome to SO. The expectation here is that you have tried to solve the problem yourself before asking. So, what have you tried? Do you know where to start? — Seva Alekseyev, Dec 02 '22 at 19:10
See if this helps: https://stackoverflow.com/questions/30023754/oracle-sql-cross-tab-query — Seva Alekseyev, Dec 02 '22 at 19:12
Alekseyev, yes. I attempted, but it failed. I've also hoard coded the Period and Year. But I need to pass the period and year dynamically. As a result, I inquired. — Venkat, Dec 03 '22 at 10:43

MT0 · Answer 1 · 2022-12-03T12:07:40.010

Use a 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
  )
)

Which, for the sample data:

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;

Outputs:

VECHICAL_NUM	VEHICAL_NAME	AUG_2022	SEP_2022	OCT_2022	NOV_2022	DEC_2022	JAN_2023	FEB_2023	DEC_2023
1111	Car	5000	5100	5300	5000	5000	5000	5000	10000
2222	Truck	10000	10000	10000	10000	10000	10000	10000	5000

fiddle

I need to pass the period and year dynamically.

If you want the current month and some previous and future months then use:

SELECT vechical_num,
       vehical_name,
       SUM(
         CASE
         WHEN period = TO_CHAR(ADD_MONTHS(SYSDATE, -4), 'Mon', 'NLS_DATE_LANGUAGE=English')
         AND  year   = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -4))
         THEN cost
         END
       ) AS prev_month_4,
       SUM(
         CASE
         WHEN period = TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'Mon', 'NLS_DATE_LANGUAGE=English')
         AND  year   = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -3))
         THEN cost
         END
       ) AS prev_month_3,
       SUM(
         CASE
         WHEN period = TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'Mon', 'NLS_DATE_LANGUAGE=English')
         AND  year   = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -2))
         THEN cost
         END
       ) AS prev_month_2,
       SUM(
         CASE
         WHEN period = TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'Mon', 'NLS_DATE_LANGUAGE=English')
         AND  year   = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, -1))
         THEN cost
         END
       ) AS prev_month_1,
       SUM(
         CASE
         WHEN period = TO_CHAR(SYSDATE, 'Mon', 'NLS_DATE_LANGUAGE=English')
         AND  year   = EXTRACT(YEAR FROM SYSDATE)
         THEN cost
         END
       ) AS current_month,
       SUM(
         CASE
         WHEN period = TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'Mon', 'NLS_DATE_LANGUAGE=English')
         AND  year   = EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, 1))
         THEN cost
         END
       ) AS next_month_1
FROM   table_name
GROUP BY
       vechical_num,
       vehical_name;

Which outputs:

VECHICAL_NUM	VEHICAL_NAME	PREV_MONTH_4	PREV_MONTH_3	PREV_MONTH_2	PREV_MONTH_1	CURRENT_MONTH	NEXT_MONTH_1
1111	Car	5000	5100	5300	5000	5000	5000
2222	Truck	10000	10000	10000	10000	10000	10000

fiddle

If you want to name the columns then don't do it in SQL. In SQL, you need to know the columns and identifiers beforehand and cannot generate them dynamically in the query. Instead, transpose the columns in your table to rows in whatever third-party client application (Java, C#, PHP, etc.) that you are using to access the database.

Thanks for responding. I need to pass the period and year dynamically. — Venkat, Dec 03 '22 at 10:46

I'd like to convert my data from rows to columns in PL/SQL. So, can anyone assist me with this?

1 Answers1