query to get Pivot in Oracle

Question

I have a table with data as shown below

    Task         Project    Amt Period
    -----------  --------   --- ------
    REVENUE 605  90020194   100 APR-20
    IE FTE       90020194   10  APR-20
    REVENUE 605  90020194   100 MAY-20
    REVENUE 605  90020194   100 JUN-20
    REVENUE 605  90020194   100 JUL-20

I need an SQL query to give the months in columns and respective amounts as shown below

    Task         Project    APR-20  MAY-20  JUN-20  JUL-20
    -----------  --------    ------  ------  ------  ------
    REVENUE 605  90020194   100     0       0       0
    IE FTE       90020194   10      0       0       0
    REVENUE 605  90020194   0       100     0       0
    REVENUE 605  90020194   0       0       100     0
    REVENUE 605  90020194   0       0       0       100

The periods are not fixed in number. I need a solution which can cope with a variable set of columns. Could anyone please help me on this.

More generally, there are already a **stack** of [answered questions regarding `[oracle]+[pivot]` on this site](https://stackoverflow.com/questions/tagged/oracle+pivot). Here is [a solution for fixed set of columns](https://stackoverflow.com/q/60670251/146325) and here is [a thread of dynamic solutions](https://stackoverflow.com/q/15491661/146325). — APC, Mar 19 '20 at 07:57
The periods are not fixed in number. I need a solution which can cope with a variable set of columns. — Anji007, Mar 19 '20 at 09:22

score 1 · Answer 1 · edited Oct 22 '22 at 21:53

1

You can achieve the desired result using the following query and please be informed that PIVOT works with a defined number of columns so you must be knowing the period to use in the query beforehand.

SELECT * FROM
(SELECT TASK, PROJECT, AMOUNT, PERIOD FROM YOUR_TABLE)
PIVOT
(SUM(AMOUNT) FOR PERIOD IN ('APR-20','MAY-20','JUN-20','JUL-20'))

edited Oct 22 '22 at 21:53

halfer

19,824
17
99
186

answered Mar 19 '20 at 08:29

Popeye

35,427
4
10
31

There really isn't much point in posting any answer until the Seeker has clarified their requirements, and especially for a topic which is already well-covered on this site. – APC Mar 19 '20 at 09:15
Many thanks for your response. I don't want to hard code the periods as these are not fixed all the time. Could you please tell me how can I achieve this without hard coded values. – Anji007 Mar 19 '20 at 09:18
1

You need to use the dynamic queries then!! – Popeye Mar 19 '20 at 10:01

query to get Pivot in Oracle

1 Answers1