sql add columns in group dynamically

98 views Asked by At

It is necessary to build a summary table based on data about the customer and their payments, where the columns will be the sequential number of the contract (contact_number) and the year (year) grouped by gender. The main condition is that contact_number and year should be dynamically generated.

Test data:

CREATE TABLE loans
(
    loan_id int,
    client_id int,
    loan_date date
);

CREATE TABLE clients
(
    client_id int,
    client_name varchar(20),
    gender varchar(20)
);

INSERT INTO CLIENTS
VALUES (1, arnold, 'male'),
       (2, lilly, 'female'),
       (3, betty, 'female'),
       (4, tom, 'male'),
       (5, jim, 'male');

INSERT INTO loans
VALUES (1, 1, '20220522'),
       (2, 2, '20220522'),
       (3, 3, '20220525'),
       (4, 4, '20220525'),
       (5, 1, '20220527'),
       (6, 2, '20220527'),
       (7, 3, '20220601'),
       (8, 1, '20220603'),
       (9, 2, '20220603'),
       (10, 1, '20220603');

Formation of columns can be done using the case when construct, but this option is not suitable due to the need to constantly add new lines in the query when adding data.

My code:

    with cte as 
    (
        select 
            l.client_id,
            loan_date,
            extract(year from loan_date) as year,
            client_name,
            gender,
            row_number() over (partition by l.client_id order by loan_date asc) as serial_number_contact
        from 
            loans l
        inner join 
            client c on l.client_id = c.client_id
    )

select 
    gender
    , year
    , contract_number
    , count(*)
from cte
group by gender, year, contract_number
order by year, contract_number

expected Output :

sex 1 contract, 2022 2 contract, 2022 3 contract, 2022
male 2 2 1
female 4 1 1

RDMBS - postgres

0

There are 0 answers