将日期列转置为行(PostgreSQL)

CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE TABLE sales (
    date DATE,
    product VARCHAR(50),
    amount NUMERIC
);

INSERT INTO sales (date, product, amount)
VALUES
    ('2022-01-01', 'Product A', 100),
    ('2022-01-01', 'Product B', 200),
    ('2022-01-02', 'Product A', 150),
    ('2022-01-02', 'Product B', 250),
    ('2022-01-03', 'Product A', 120),
    ('2022-01-03', 'Product B', 180);

SELECT *
FROM crosstab(
    'SELECT date, product, amount
     FROM sales
     ORDER BY 1, 2',
    'SELECT DISTINCT product
     FROM sales
     ORDER BY 1'
) AS ct (date DATE, product1 NUMERIC, product2 NUMERIC);

    date    | product1 | product2
------------+----------+----------
 2022-01-01 |      100 |      200
 2022-01-02 |      150 |      250
 2022-01-03 |      120 |      180