Chapter 1: pivoting rows into columns; ~/Folkens

Rows into columns. One row per month needs to become one row per year with a column per month. The book calls this a pivot.

// the problem

CREATE TABLE sales (
    year  INT,
    month VARCHAR(3),
    total DECIMAL(10,2)
);

-- year | month | total
-- 2002 | Jan   | 1800.00
-- 2002 | Feb   | 2400.00
-- 2002 | Mar   | 1950.00
-- 2002 | Q1    | 6150.00  <- quarterly rollup also present

Target output:

-- year | Jan     | Feb     | Mar     | ...
-- 2002 | 1800.00 | 2400.00 | 1950.00 | ...

// the original approach (2002)

No PIVOT keyword meant CASE inside an aggregate; one expression per output column:

SELECT
    year,
    SUM(CASE WHEN month = 'Jan' THEN total ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN total ELSE 0 END) AS Feb,
    SUM(CASE WHEN month = 'Mar' THEN total ELSE 0 END) AS Mar,
    SUM(CASE WHEN month = 'Apr' THEN total ELSE 0 END) AS Apr,
    SUM(CASE WHEN month = 'May' THEN total ELSE 0 END) AS May,
    SUM(CASE WHEN month = 'Jun' THEN total ELSE 0 END) AS Jun,
    SUM(CASE WHEN month = 'Jul' THEN total ELSE 0 END) AS Jul,
    SUM(CASE WHEN month = 'Aug' THEN total ELSE 0 END) AS Aug,
    SUM(CASE WHEN month = 'Sep' THEN total ELSE 0 END) AS Sep,
    SUM(CASE WHEN month = 'Oct' THEN total ELSE 0 END) AS Oct,
    SUM(CASE WHEN month = 'Nov' THEN total ELSE 0 END) AS Nov,
    SUM(CASE WHEN month = 'Dec' THEN total ELSE 0 END) AS Dec
FROM sales
WHERE month != 'Q1'
GROUP BY year;

Tedious and brittle. Add a new month abbreviation and it silently disappears from output.

// modern t-sql: the pivot operator

SQL Server 2005 added PIVOT:

SELECT year, Jan, Feb, Mar, Apr, May, Jun,
             Jul, Aug, Sep, Oct, Nov, Dec
FROM sales
WHERE month != 'Q1'
PIVOT (
    SUM(total)
    FOR month IN (Jan, Feb, Mar, Apr, May, Jun,
                  Jul, Aug, Sep, Oct, Nov, Dec)
) AS pvt;

Cleaner, but the column list is still hardcoded. Dynamic columns still require string-building and EXEC sp_executesql.

// postgres

No PIVOT keyword. Use FILTER:

SELECT
    year,
    SUM(total) FILTER (WHERE month = 'Jan') AS "Jan",
    SUM(total) FILTER (WHERE month = 'Feb') AS "Feb",
    SUM(total) FILTER (WHERE month = 'Mar') AS "Mar",
    -- ...
    SUM(total) FILTER (WHERE month = 'Dec') AS "Dec"
FROM sales
WHERE month != 'Q1'
GROUP BY year;

For dynamic pivots, crosstab() via the tablefunc extension works but has an awkward interface.

// oracle

SELECT *
FROM sales
WHERE month != 'Q1'
PIVOT (
    SUM(total)
    FOR month IN ('Jan' AS Jan, 'Feb' AS Feb, 'Mar' AS Mar,
                  'Apr' AS Apr, 'May' AS May, 'Jun' AS Jun,
                  'Jul' AS Jul, 'Aug' AS Aug, 'Sep' AS Sep,
                  'Oct' AS Oct, 'Nov' AS Nov, 'Dec' AS Dec)
)
ORDER BY year;

Oracle's PIVOT also supports ANY for dynamic column generation, with caveats.

// sqlite

No PIVOT. FILTER is supported from 3.30 (2019) and works identically to PostgreSQL. Use CASE for older versions.

// summary

SQL cannot produce a dynamically-columned result set without dynamic SQL. FILTER (Postgres, SQLite) and PIVOT (SQL Server, Oracle) are cleaner than the CASE wall, but still require enumerating columns up front.

If the number of output columns is truly dynamic, the pivot belongs in the application layer, not the query.

// dynamic pivot in c#: linq

record SaleRow(int Year, string Month, decimal Total);

var pivoted = rows
    .Where(r => r.Month != "Q1")
    .GroupBy(r => r.Year)
    .Select(g => new
    {
        Year    = g.Key,
        Columns = g.ToDictionary(r => r.Month, r => r.Total)
    })
    .OrderBy(r => r.Year)
    .ToList();

foreach (var row in pivoted)
{
    Console.WriteLine($"{row.Year}: Jan={row.Columns.GetValueOrDefault("Jan"):C}");
}

Assumes one row per month per year. For duplicates, replace ToDictionary with:

Columns = g.GroupBy(r => r.Month)
            .ToDictionary(m => m.Key, m => m.Sum(r => r.Total))

// dynamic pivot in c#: optimized

The LINQ version makes two passes per group and allocates a dictionary per year. For large result sets, a single-pass loop is faster with less GC pressure:

var pivot = new Dictionary<int, Dictionary<string, decimal>>();

foreach (var row in rows)
{
    if (row.Month == "Q1") continue;

    if (!pivot.TryGetValue(row.Year, out var months))
    {
        months = new Dictionary<string, decimal>(12); // pre-size: 12 months
        pivot[row.Year] = months;
    }

    months[row.Month] = months.TryGetValue(row.Month, out var existing)
        ? existing + row.Total
        : row.Total;
}

var allMonths = new List<string> { "Jan","Feb","Mar","Apr","May","Jun",
                                    "Jul","Aug","Sep","Oct","Nov","Dec" };

foreach (var (year, months) in pivot.OrderBy(kv => kv.Key))
{
    var values = allMonths.Select(m => months.GetValueOrDefault(m));
    Console.WriteLine($"{year}: {string.Join(" | ", values)}");
}

Differences vs. LINQ:

Single pass - no intermediate groupings
Pre-sized inner dictionary - avoids rehashing
No closure/enumerator allocation - LINQ's chain allocates per element; the loop does not

For typical reporting queries this difference is irrelevant. It matters at millions of rows in bulk exports, dashboards, or ETL pipelines.