Tablefunc

-- Basic crosstab syntax
SELECT * FROM crosstab(
    source_sql text,   -- SQL query returning (row_name, category, value)
    category_sql text  -- SQL query returning distinct categories
) AS ct (
    row_name text,     -- Name of the row
    category1 text,    -- First category column
    category2 text,    -- Second category column
    ...
);

-- Create sample data
CREATE TABLE quarterly_sales (
    tenant_id uuid,
    year int,
    quarter text,
    sales numeric
);

INSERT INTO quarterly_sales VALUES
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q1', 100),
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q2', 150),
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q3', 130),
    ('11111111-1111-1111-1111-111111111111', 2023, 'Q4', 180),
    ('11111111-1111-1111-1111-111111111111', 2024, 'Q1', 120),
    ('11111111-1111-1111-1111-111111111111', 2024, 'Q2', 160);

-- Create cross tab of sales by year and quarter
SELECT * FROM crosstab(
    'SELECT year, quarter, sales
     FROM quarterly_sales
     ORDER BY 1,2',
    'SELECT DISTINCT quarter
     FROM quarterly_sales
     ORDER BY 1'
) AS ct (
    year int,
    "Q1" numeric,
    "Q2" numeric,
    "Q3" numeric,
    "Q4" numeric
);

 year | Q1  | Q2  | Q3  | Q4
------+-----+-----+-----+-----
 2023 | 100 | 150 | 130 | 180
 2024 | 120 | 160 | null| null

-- Generate 5 normal random numbers
SELECT * FROM normal_rand(
    5,    -- number of rows
    15    -- standard deviation
);

-- Create sample employee hierarchy
CREATE TABLE employees (
    tenant_id uuid,
    employee_id int,
    name text,
    manager_id int,
    PRIMARY KEY (tenant_id, employee_id)
);

INSERT INTO employees VALUES
    ('11111111-1111-1111-1111-111111111111', 1, 'CEO', NULL),
    ('11111111-1111-1111-1111-111111111111', 2, 'VP Sales', 1),
    ('11111111-1111-1111-1111-111111111111', 3, 'VP Engineering', 1),
    ('11111111-1111-1111-1111-111111111111', 4, 'Sales Manager', 2),
    ('11111111-1111-1111-1111-111111111111', 5, 'Engineer', 3);

-- Query hierarchical employee structure
-- Order of results is not guaranteed
SELECT * FROM connectby(
    'employees', 'employee_id', 'manager_id', '1', 0, '>')
 AS t(employee_id int, manager_id int, level int, branch text);

 employee_id | manager_id | level | branch
-------------+------------+-------+--------
           1 |            |     0 | 1
           2 |          1 |     1 | 1>2
           4 |          2 |     2 | 1>2>4
           3 |          1 |     1 | 1>3
           5 |          3 |     2 | 1>3>5