Creating pivot table (columns to rows) in postgresql

userUBAf 0
Viewed 23

I would like to create a pivot table from a PostgreSQL table below. (Also link: Fiddle)

CREATE TABLE sf1
    ("calendardate" varchar(10), "ticker" varchar(4), "revenueusd" int);
INSERT INTO sf1
    ("calendardate", "ticker", "revenueusd")
VALUES
('12/2021', 'DIS', '218190'),
('12/2021', 'ADBE', '41100'),
('12/2021', 'AAPL', '1239450'),
('03/2022', 'AAPL', '972780'),
('03/2022', 'DIS', '192490'),
('03/2022', 'ADBE', '42620'),
('06/2022', 'ADBE', '43860'),
('06/2022', 'AAPL', '829590'),
('06/2022', 'DIS', '215040')

SELECT 
    calendardate,
    ticker,
    revenueusd
FROM
    sf1
ORDER BY calendardate ASC

The code result looks like this:

date ticker revenue
12/2021 DIS 218190
12/2021 ADBE 41100
12/2021 AAPL 1239450
03/2022 AAPL 972780
03/2022 DIS 192490
03/2022 ADBE 42620
06/2022 ADBE 43860
06/2022 AAPL 829590
06/2022 DIS 215040

The goal would be to transform it like this:

ticker/date 12/2021 03/2022 06/2022
DIS 218190 192490 215040
ADBE 41100 42620 43860
AAPL 1239450 972780 829590

Is there any way to wire this while the final ticker rows and date columns also would change in number?

The data comes from Nasdaq, Core US Fundamentals Data

stock pivot sql postgresql
1 Answers

Refer query below -

SELECT *
FROM crosstab('select  ticker, to_date(date1,''mm/yyyy'') date1,
     revenue from sf1 order by 1 desc,2 ')
    AS ct("ticker/date" text,
          "12/2021" integer,
          "03/2022" integer,
          "06/2022" integer);

Fiddle.

user92oW0
Related