nlqdb

Solve · Analysts and PMs

How do I pivot rows into columns in SQL without writing a crosstab query?

If you need a pivot table — rows turned into columns, like revenue per product with one column per month — ask in plain English instead of hand-writing a crosstab. nlqdb compiles the conditional aggregation, runs it in Postgres, and shows the SQL so you can verify the buckets.

PMs, ops, and analysts hit this every reporting cycle — revenue per product with months across the top, signups per plan by week, counts per status as columns. It's the pivot / crosstab problem, and the SQL is fiddly: Postgres has no `PIVOT` keyword, so the answer is conditional aggregation (`SUM(...) FILTER (WHERE ...)` or `SUM(CASE WHEN ... THEN ...)`) — one expression per output column — or the `crosstab()` function from the `tablefunc` extension. Either way it gets re-Googled every time or filed as a data ticket, and a plain `GROUP BY` gives you tall rows, not the wide table the spreadsheet wants.

The snippet that solves it.

> total revenue per product with one column for each month this year

What nlqdb does for this

  • Ask 'revenue per product, one column per month'; nlqdb compiles the conditional aggregation (`SUM(...) FILTER (WHERE month = ...)`) and runs it in Postgres.
  • Every answer returns the pivoted table plus the compiled SQL under a trace toggle (`SK-WEB-005`) — confirm each column's bucket before trusting it.
  • Works no-code over a provisioned demo, or connect a Postgres you already run (BYO connect, `SK-DBCONN-001`) to pivot your real data.
  • Repeated pivots hit the plan cache — content-addressed on `(goal-fingerprint, schema-hash)` (`GLOBAL-006`) — so the same wide report returns in single-digit ms.

Drop into any HTML page

<nlq-data goal="total revenue per product with one column for each month this year"></nlq-data>

The exact wide report you'd otherwise hand-write with one CASE expression per month is one English goal here, with the SQL shown so you can check each bucket.

What this replaces

What nlqdb doesn't try to do here

  • The pivot columns must be ones you can name — 'months this year', 'these three statuses'. A fully dynamic crosstab over a category set unknown until query time needs that list resolved first; nlqdb doesn't generate columns from values it hasn't seen.
  • nlqdb returns the pivoted table with a read-only SELECT — it's not a BI tool maintaining a live crosstab dashboard or refreshing the wide report on a schedule. That's a scheduled job's work.
  • The public `<nlq-data>` embed is read-scoped — it reshapes and surfaces rows, it doesn't write. No write key belongs in client HTML; loading the data goes through the SDK or `POST /v1/run`.

Questions buyers ask

How do I pivot rows into columns without writing a crosstab query?
Ask in plain English — 'revenue per product, one column per month.' nlqdb compiles the conditional aggregation (one `SUM(...) FILTER (WHERE ...)` per output column), runs it in Postgres, and returns the wide table plus the SQL it ran. You get the pivot without hand-writing a CASE expression per column or wiring the `tablefunc` extension. The honest limit: the columns must be ones you can name.
Does nlqdb use SQL Server's PIVOT keyword or Postgres crosstab?
Neither by default — nlqdb is Postgres-first, and the portable pivot pattern there is conditional aggregation: `SUM(amount) FILTER (WHERE month = 'Jan')` as one column, repeated per bucket. That needs no `PIVOT` keyword (Postgres has none) and no `tablefunc` extension. The compiled SQL shows under the trace toggle so you can confirm each column maps to the bucket you meant.
Can I pivot a Postgres database I already run?
Yes — connect it with the signed-in BYO connect verb (`nlq db connect`, `SK-DBCONN-001`; see /solve/query-existing-postgres-in-natural-language) and ask for the wide report in place, no ETL into a separate store. The honest limits: BYO connect is signed-in only (not the public embed), and nlqdb returns the pivot with a read-only query — it doesn't persist a materialized crosstab for you.
Why not just pivot in Excel or a spreadsheet?
A spreadsheet pivot table works once you've exported the rows — but it's a manual copy each refresh, capped by row count, and detached from the live data. Asking nlqdb runs the aggregation in the database against current rows and shows the SQL, so the wide report is reproducible and auditable rather than a stale paste. Export the result to a sheet afterward if you like.

Where this pain shows up in public

Enduring discussion hubs where you can verify the theme without taking our word for it. We don't quote individual posts; we cite search-result and subreddit URLs that stay live as new threads land.

Try nlqdb in 30 seconds

No sign-in. The anonymous database lasts 72 hours; adopt it with one click if you keep it.

Start with a goal →

Looking at this from a different angle? Browse all solve pages or browse competitor comparisons.