Inner join on unknown amount of rows-CodePudding

I have the following table in my DB (postgres)

CREATE TABLE "quarterly" (
    "ticker" varchar,
    "quarter_date" date,
    "statement_type" int4,
    "statement" jsonb,
    PRIMARY KEY ("ticker", "quarter_date", "statement_type")
);

Now, I'd like to somehow write query that would connect all statements with the same date into sigle row. Ideal output is something like:

ticker, quarter_date, statement1, statement2, statement3

Even better if i could name statements columns based on type they have. My first try to this problem was the following code:

select Q1.ticker, Q1.quarter_date, Q1.statement, Q2.statement 
from quarterly Q1, quarterly Q2 
where Q1.quarter_date = Q2.quarter_date and Q1.ticker = Q2.ticker

I'm really new to sql, so I don't know how to extend this approach to merge more than 2 statements. I now have 4 'statement_types' and might have more in the future. It would be great if that could be done with just sql and not in my backend. Is there any way to do that?

CodePudding user response：

Maybe use crosstab with row_number () function

SELECT * FROM crosstab
(
'select 
   ticker,
   quarter_date,
   row_number() over (partition by ticker,quarter_date order by statement_type) as r, 
   statement
   from quarterly order by 1,2,3')
AS ct (
   ticker varchar(15), 
   quarter_date date,
   statement1 jsonb , 
   statement2 jsonb ,
   statement3 jsonb,
   statement4 jsonb);