[btown]

One of the things that LLMs "excel" at, pun very much intended, is this exact pattern - creating filtered aggregates for a finite set of columns, and using this at the end of a CTE!

OP's example, for reference, was:

    SELECT rank,
    MAX(CASE WHEN file = 1 THEN COALESCE(piece, '·') END) AS a,
    MAX(CASE WHEN file = 2 THEN COALESCE(piece, '·') END) AS b,
    MAX(CASE WHEN file = 3 THEN COALESCE(piece, '·') END) AS c,
    MAX(CASE WHEN file = 4 THEN COALESCE(piece, '·') END) AS d,

This pattern is incredible for generating financial model drivers (where every column is a calendar/fiscal month/quarter/year, and every row is a different type of statistic/measure).

The broader pattern is, in successive CTEs:

1. Group by Date w/ Aggregates

2. "Melt" to [optional groupings +] month + measure_name + value tuples:

    select group, month, '# Bookings' as measure_name, num_bookings as value from base_data
    UNION ALL
    select group, month, 'Revenue', total_revenue from base_data

3. Then "pivot":

    MAX(CASE WHEN month = '2019-01' THEN value END) AS "2019-01",
    MAX(CASE WHEN month = '2019-02' THEN value END) AS "2019-02",
    MAX(CASE WHEN month = '2019-03' THEN value END) AS "2019-03",

And what you get is a full analysis table, with arbitrary groupings, that can be dropped into an Excel model in a way that makes life easy for business teams.

And while the column breakouts are painful to type out by hand - they're very amenable to LLM generation!