Dataset Aggregate & Pivot

Dataset Aggregate & Pivot (Remote MCP Server)

SQL GROUP BY and spreadsheet pivot tables for messy JSON rows, in a single tool call. Hand it a list of rows from a scraper, an API or a spreadsheet, say what to group by and what to compute, and it hands back one clean summary row per group plus an exact account of anything it skipped.

Built for AI agents. No install, no API key, no signup. Connect by URL and call it.

https://dataset-aggregate-pivot.nerolabs.workers.dev/mcp

Free to use while in early access.

What it does

One call runs the whole summary, in this order:

1. Group the rows by one or several fields (dot paths like address.city work), or leave the group fields empty to summarise every row into one. Add a date bucket to group a date or timestamp by day, ISO week, month, quarter or year (orderedAt becomes orderedAt_month = 2026-08).
2. Aggregate each group with any of 11 functions: count, countDistinct, sum, avg, min, max, median, first, last, list and listDistinct, each with its own output column name.
3. Pivot one field's distinct values into columns: group by region, pivot on product, fill the cells with the sum of amount, and get one row per region with a column per product, zero-filled where a combination has no rows.
4. Sort by any output column, keep the top N groups, and add a grand-total row that still covers every input row.

Messy data is the normal case. South, south and SOUTH land in one group with one label. "$1,234.50", "49 USD", "1.234,50" and "(300)" are read as numbers. Values that genuinely are not numbers, like "n/a", are never guessed at: they are left out and counted in the summary, and a misspelled field name comes back as a warning instead of a silently empty result.

Tools

Connect

Claude Code

claude mcp add --transport http dataset-aggregate-pivot https://dataset-aggregate-pivot.nerolabs.workers.dev/mcp

Claude Desktop / claude.ai: Settings, Connectors, Add custom connector, paste the URL above.

Cursor, Windsurf, VS Code and other MCP clients

{
  "mcpServers": {
    "dataset-aggregate-pivot": {
      "url": "https://dataset-aggregate-pivot.nerolabs.workers.dev/mcp"
    }
  }
}

Example

Eight messy order rows go in, with orders per region and each product's revenue pivoted into its own column:

{
  "rows": [
    {"orderId": 1001, "region": "North", "product": "Widget", "amount": "$1,200.00", "orderedAt": "2026-07-03"},
    {"orderId": 1002, "region": "North", "product": "Gadget", "amount": 350, "orderedAt": "2026-07-18"},
    {"orderId": 1003, "region": "South", "product": "Widget", "amount": "890.50", "orderedAt": "2026-07-22"},
    {"orderId": 1004, "region": "south", "product": "Gizmo", "amount": 120, "orderedAt": "2026-08-02"},
    {"orderId": 1005, "region": "East", "product": "Widget", "amount": 2400, "orderedAt": "2026-08-05"},
    {"orderId": 1006, "region": "East", "product": "Gadget", "amount": "n/a", "orderedAt": "2026-08-09"},
    {"orderId": 1007, "region": "North", "product": "Gizmo", "amount": 75, "orderedAt": "2026-08-11"},
    {"orderId": 1008, "region": "", "product": "Widget", "amount": 410, "orderedAt": "2026-08-14"}
  ],
  "groupByFields": ["region"],
  "aggregations": [{"function": "count", "alias": "orders"}],
  "pivotField": "product",
  "pivotValueField": "amount",
  "pivotFunction": "sum"
}

Four summary rows come out. South and south became one group, "$1,200.00" summed as 1200, the row with no region is kept visibly as (blank), and the "n/a" amount was skipped and reported rather than treated as a number:

{
  "rows": [
    {"region": "(blank)", "orders": 1, "Gadget": 0, "Gizmo": 0, "Widget": 410},
    {"region": "East", "orders": 2, "Gadget": 0, "Gizmo": 0, "Widget": 2400},
    {"region": "North", "orders": 3, "Gadget": 350, "Gizmo": 75, "Widget": 1200},
    {"region": "South", "orders": 2, "Gadget": 0, "Gizmo": 120, "Widget": 890.5}
  ],
  "summary": {
    "inputRowCount": 8,
    "groupCount": 4,
    "outputRowCount": 4,
    "pivot": {"field": "product", "valueField": "amount", "function": "sum", "distinctValues": 3},
    "columns": ["region", "orders", "Gadget", "Gizmo", "Widget"],
    "skippedValues": {"pivot:Gadget": 1}
  }
}

Add "sortBy": "orders", "sortDirection": "desc", "topN": 10, "includeTotalsRow": true to the same call for a top 10 with a grand total, or "dateBucketField": "orderedAt" for one row per region per month.

Limits

• Up to 500 rows per call. Anything larger returns a clear message rather than failing silently. Batches can be summarised separately for sum, count, min and max, and an average rebuilt as total sum divided by total count, but a median or a distinct count cannot be combined across batches.
• Up to 50 pivot columns and 5,000 pivot cells (groups multiplied by pivot columns) per call, so a pivot on a near-unique field such as an ID is refused with advice instead of returning hundreds of columns.
• Up to 20 aggregations per call.
• Rows are passed inline as JSON.

Privacy

Your rows are processed in memory and never stored. To see which tools get used, each call records the tool name, row counts, whether it succeeded, the client name your app reports, the country and a one-way hashed caller ID. Your data, your arguments and your IP address are never kept in that log.

Also available

The same engine runs on the Apify Store as Dataset Aggregate, Group By & Pivot, which also reads Apify datasets, CSV, TSV, Excel, JSON and JSON Lines files and Google Sheets by URL, handles up to 200,000 rows per run, exports the summary as a CSV or Excel file, appends it to a named dataset that accumulates across scheduled runs, and posts it to a webhook.

Built by Nero Labs.