How do I pivot data by month and region with AI?
Recipe verified on
Ran on the public demo (read-only)
Short answer
Ask for the value grouped by both month and region: one query returns a row per month and region, which is a pivot table once the regions become columns. Your AI can lay it out, and a saved report can open it in the workbench's pivot directly.
Steps
- Connect your assistant to the public demo. It's a read-only instance with 10 million synthetic orders,
so you can try this before you have your own. In Claude Code:
claude mcp add --transport http mcpdata-demo https://demo.mcpdata.org/mcp --header "Authorization: Bearer cdb_live_PkVoldus8qcE-hqffjajNZzQwiA7rTHMoR4RLSAxsCE"
The key is public on purpose: it can only read the demo's sample data. For Claude Desktop, Cursor and other apps, see Connect your AI.
- Ask your question. Give your AI this prompt:
Using mcpdata, revenue by month and region for 2025 from Demo.Orders, as a table with a column per region.
What your AI does: the tool call it makes for this prompt, and what came back
query(table_name="Demo.Orders", filters=[{"column": "order_year", "op": "eq", "value": 2025}], group_by=["order_month", "region"], agg=[{"func": "sum", "column": "revenue", "alias": "revenue"}], sort=[{"column": "order_month", "desc": false}, {"column": "region", "desc": false}], limit=100) → 36 rows
| order_month | APAC | Americas | EMEA |
|---|---|---|---|
| 2025-01 | 31,308,523 | 65,707,869 | 59,462,781 |
| 2025-02 | 28,851,954 | 60,315,307 | 54,646,255 |
| 2025-03 | 32,284,182 | 67,671,765 | 61,718,086 |
| 2025-04 | 31,132,655 | 65,476,850 | 59,046,685 |
| 2025-05 | 33,213,488 | 69,285,388 | 62,213,675 |
| 2025-06 | 33,667,736 | 69,698,346 | 63,292,061 |
| 2025-07 | 36,987,809 | 76,946,704 | 69,931,726 |
| 2025-08 | 35,804,577 | 74,864,596 | 67,624,060 |
| 2025-09 | 33,395,094 | 69,275,222 | 62,673,023 |
| 2025-10 | 34,904,032 | 72,328,853 | 65,906,703 |
| 2025-11 | 38,661,087 | 81,419,665 | 73,598,030 |
| 2025-12 | 43,656,307 | 91,427,037 | 82,143,808 |
Synthetic data: the demo's orders are generated, not real customers.
The assistant needs mcpdata connected (step 1) to run the query.
Why this works
A pivot is a group-by on two columns, turned sideways. Doing the grouping in the database means every cell is an exact total over all the rows; the assistant only rearranges 36 rows into a 12 by 3 table.