> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Tablefunc

> Table function utilities for cross tabulation and pivot operations

The `tablefunc` extension provides a set of functions for manipulating tables, including cross tabulation, pivoting, and connecting tables.
Your Nile database arrives with the tablefunc extension already enabled.

## Overview

The tablefunc extension provides several useful functions:

* `crosstab`: Creates pivot tables and cross tabulations
* `normal_rand`: Generates normally distributed random numbers
* `connectby`: Implements hierarchical queries

## Cross Tabulation Functions

### Basic Crosstab

The `crosstab` function transforms row-oriented data into a cross-tabulation format (pivot table).

```sql theme={null}
-- 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
    ...
);
```

### Example: Sales by Quarter

```sql theme={null}
-- 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
);
```

Result:

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

## Normal Random Numbers

The `normal_rand` function generates normally distributed random numbers:

```sql theme={null}
-- Generate 5 normal random numbers
SELECT * FROM normal_rand(
    5,    -- number of rows
    15    -- standard deviation
);
```

## Hierarchical Queries

The `connectby` function helps create hierarchical queries.
Let's create a sample employee hierarchy and query it:

```sql theme={null}
-- 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);
```

Result:

```
 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
```

## Additional Resources

Tablefunc is a powerful extension for working with tables and hierarchies.
We only covered a few basic examples here, but there are many more options available.
You can read the rest in [PostgreSQL tablefunc Documentation](https://www.postgresql.org/docs/current/tablefunc.html)