How to create sql table that changes dynamically the order of the columns based on current date/month?

Question

My goal is to have a table with the following structure:

Company name	December 2021	November 2021	October 2021	...	January 2021
Google	70	30	20	...	80
Amazon	30	45	34	...	24
...

It contains for each company the credit of all the clients within that particular month.

I want to keep track only of the last 12 months (the table gets updated monthly). In January 2022 I would want the column order to change as follows:

Company name	January 2022	December 2021	November 2021	...	February 2021
Google	25	70	30	...	14
Amazon	80	30	45	...	33
...

In February 2022 it would change in:

Company name	February 2022	January 2022	December 2021	...	January 2021
Google	37	25	70	...	20
Amazon	42	80	30	...	21
...

Assuming that I can retrieve the credit values from a table with the following structure:

Company name	Month	Value
Google	December 2021	70
Amazon	December 2021	30
Google	November 2021	30
Amazon	November 2021	45
...

do you know how to do that in SQL?

Isn't better to store all data and all month , and later just write correct select with data based on current month? Just -12 month . Otherwise you need to write trigger or procedure that when job starts per month - it delete last month and add new one — Adamszsz
Do you actually have a table with a column for each month - and if so how many columns, spanning how many years - or are you actually looking at a pivoted view against a more manageable table with a row per month (or many rows per month, and the view is aggregating at month level)? The DDL for your existing table(s)/view(s) would be helpful; as would showing how you 'retrieve the credit values' now.. — Alex Poole
This would be a very poor and painful design. BBetter store the data in records. — Wernfried Domscheit
@AlexPoole the second option :) I added the structure of the source table in the post, hope it helps! — chattershuts
Most of what you need can be done in standard SQL. If you want to show the last 12 months, dynamically based on current date, that can be done easily. What cannot be done easily is to dynamically change the column names. You either need dynamic SQL (best to do that from your reporting tool, rather than to write your own dynamic code), or you can use generic column names like "last month", "two months ago" etc.. — mathguy

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2021-07-29T14:31:59

Assuming you have a table with a date (i.e. a day of the month, e.g. the last day of the month) stored:

Company	Month	Value
Google	2021-06-30	70
Amazon	2021-06-30	30
Google	2021-05-31	30
Amazon	2021-05-31	45
...	...	...
Amazon	2020-01-31	123

and you want the last twelve months, you can use a pivot query with the PIVOT clause or with conditional aggregation. I am using the latter in below query:

    select 
        company,
        sum(case when trunc(month, 'mm') = trunc(sysdate, 'mm') - interval '12' month then value end) as "12 months ago",
        sum(case when trunc(month, 'mm') = trunc(sysdate, 'mm') - interval '11' month then value end) as "11 months ago",
        ...
        sum(case when trunc(month, 'mm') = trunc(sysdate, 'mm') - interval  '2' month then value end) as  "2 months ago",
        sum(case when trunc(month, 'mm') = trunc(sysdate, 'mm') - interval  '1' month then value end) as  "1 month ago"
    from mytable
    where month < trunc(sysdate, 'mm') and month >= trunc(sysdate, 'mm') - interval '12' month
    group by company
order by company;

When displaying this result in a grid in your app, you can of course change the column titles to the months in question.

How to create sql table that changes dynamically the order of the columns based on current date/month?

1 Answers