# SQL Monthly Pivot Query Generator Using Conditional Aggregation

> Source: https://netray.co/tools/sql-monthly-pivot-conditional-aggregation-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a month-by-month cross-tab of any ERP amount, such as usage by item or sales by customer, using portable CASE and SUM logic.

## Category

developer

## Audience

- ERP analyst
- Finance analyst
- Planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Table or view

**Key:** table

**Type:** text

**Default:** dbo.inventory_transactions

#### Row label column

**Key:** group_col

**Type:** text

**Default:** item_code

#### Date column

**Key:** date_col

**Type:** text

**Default:** transaction_date

#### Value column to sum

**Key:** value_col

**Type:** text

**Default:** quantity

#### Start date (inclusive)

**Key:** date_from

**Type:** date

**Default:** 2025-01-01

#### End date (exclusive)

**Key:** date_to

**Type:** date

**Default:** 2026-01-01

#### Extra filter (without AND)

**Key:** filter

**Type:** text

**Default:** transaction_type = 'ISSUE'

**Blocks:**

#### Month columns with conditional aggregation

**Body:** SELECT
    {{group_col}},
    SUM(CASE WHEN MONTH({{date_col}}) = 1  THEN {{value_col}} ELSE 0 END) AS m01_jan,
    SUM(CASE WHEN MONTH({{date_col}}) = 2  THEN {{value_col}} ELSE 0 END) AS m02_feb,
    SUM(CASE WHEN MONTH({{date_col}}) = 3  THEN {{value_col}} ELSE 0 END) AS m03_mar,
    SUM(CASE WHEN MONTH({{date_col}}) = 4  THEN {{value_col}} ELSE 0 END) AS m04_apr,
    SUM(CASE WHEN MONTH({{date_col}}) = 5  THEN {{value_col}} ELSE 0 END) AS m05_may,
    SUM(CASE WHEN MONTH({{date_col}}) = 6  THEN {{value_col}} ELSE 0 END) AS m06_jun,
    SUM(CASE WHEN MONTH({{date_col}}) = 7  THEN {{value_col}} ELSE 0 END) AS m07_jul,
    SUM(CASE WHEN MONTH({{date_col}}) = 8  THEN {{value_col}} ELSE 0 END) AS m08_aug,
    SUM(CASE WHEN MONTH({{date_col}}) = 9  THEN {{value_col}} ELSE 0 END) AS m09_sep,
    SUM(CASE WHEN MONTH({{date_col}}) = 10 THEN {{value_col}} ELSE 0 END) AS m10_oct,
    SUM(CASE WHEN MONTH({{date_col}}) = 11 THEN {{value_col}} ELSE 0 END) AS m11_nov,
    SUM(CASE WHEN MONTH({{date_col}}) = 12 THEN {{value_col}} ELSE 0 END) AS m12_dec,
    SUM({{value_col}}) AS period_total
FROM {{table}}
WHERE {{date_col}} >= '{{date_from}}'
  AND {{date_col}} <  '{{date_to}}'
  AND {{filter}}
GROUP BY {{group_col}}
ORDER BY period_total DESC;

#### Average monthly usage and months with activity

**Body:** WITH monthly AS (
    SELECT
        {{group_col}} AS grp,
        YEAR({{date_col}}) AS yr,
        MONTH({{date_col}}) AS mth,
        SUM({{value_col}}) AS month_value
    FROM {{table}}
    WHERE {{date_col}} >= '{{date_from}}'
      AND {{date_col}} <  '{{date_to}}'
      AND {{filter}}
    GROUP BY {{group_col}}, YEAR({{date_col}}), MONTH({{date_col}})
)
SELECT
    grp,
    COUNT(*) AS months_with_activity,
    SUM(month_value) AS total_value,
    AVG(month_value) AS avg_active_month,
    MIN(month_value) AS min_month,
    MAX(month_value) AS max_month
FROM monthly
GROUP BY grp
ORDER BY total_value DESC;

#### Unpivoted monthly rows for charting

**Body:** SELECT
    {{group_col}},
    YEAR({{date_col}}) AS yr,
    MONTH({{date_col}}) AS mth,
    SUM({{value_col}}) AS month_value
FROM {{table}}
WHERE {{date_col}} >= '{{date_from}}'
  AND {{date_col}} <  '{{date_to}}'
  AND {{filter}}
GROUP BY {{group_col}}, YEAR({{date_col}}), MONTH({{date_col}})
ORDER BY {{group_col}}, yr, mth;

**Notes:** The first block assumes a window of at most 12 months in one calendar year. For a range spanning two years, use the third block or add YEAR to the CASE test, otherwise January of both years is merged. MONTH() and YEAR() are SQL Server, MySQL and PostgreSQL friendly; on Oracle use EXTRACT(MONTH FROM col). Date literals may need your engine's format.

## Content

**Intro:** Planners and finance analysts keep asking for the same report: months across the top, items or customers down the side. This generator writes that cross-tab with plain CASE and SUM so it runs in any SQL database.

**Sections:**

#### How to use the generator

**Body:** Enter the table or view, the column that labels each row, the date column and the value to add up. Set the start date inclusive and the end date exclusive, and add any filter such as the transaction type. Copy the first block for the cross-tab. Run it on a small group first and compare one row with the ERP's own inquiry screen. If it matches, widen the filter and paste the result into Excel for charts or a rolling forecast.

#### Why conditional aggregation

**Body:** SQL Server has a PIVOT operator and some engines have crosstab extensions, but they differ between databases and need the column list in advance. Conditional aggregation uses SUM over a CASE per month, which is plain ANSI SQL, easy to read and easy to adjust, for example to add a quantity and a value pair. The cost is a wide query to maintain, which is why a generator helps. The date range should use a half-open interval so that the last day is never lost when the column has a time part.

#### Reading the result

**Body:** The period total column sorts the biggest movers first. Use the second block when you need average usage for reorder points: it averages only months with activity, so an item used in 3 of 12 months looks very different from an item used every month. Compare average of active months with the total divided by the number of months to see lumpiness. That gap often explains why a safety stock calculation based on a flat average fails for lumpy demand.

#### Common mistakes

**Body:** The most common error is a range crossing two calendar years in the first block, which merges months. Others are using BETWEEN with datetime values, which drops the final day, forgetting to filter reversals or voided rows, and summing a signed quantity where you meant issues only. Check the filter against a transaction type list in your data dictionary before trusting the totals.

**Cta:** If you want these cross-tabs scheduled from your ERP into a shared sheet or dashboard, we can build and maintain that for you.

**Faq:**

#### Why not use the PIVOT operator?

**Answer:** PIVOT works in SQL Server and Oracle but with different syntax and a fixed column list. Conditional aggregation is portable, easier to extend with extra measures and just as fast in most ERP reporting queries.

#### Can I do a rolling 12 months instead of a calendar year?

**Answer:** Yes. Use the unpivoted third block with a date range of the last 12 months, then pivot in Excel. Or replace the MONTH test with a month offset from the end date computed in your engine.

#### How do I avoid dropping the last day of the range?

**Answer:** Use a half-open range: on or after the start and strictly before the day after the end. Avoid BETWEEN with datetime columns because midnight ends the range.

#### Will this be slow on big tables?

**Answer:** It scans the rows in the date window once. An index on the date column, ideally including the group and value columns, helps. Filter by date range first and avoid wrapping the date column in functions in the WHERE clause.

## Search terms

sql pivot months columns, sql conditional aggregation, monthly usage by item sql, sales by month by customer sql, sql server pivot alternative, cross tab query erp, case when sum month sql, 12 month rolling usage query

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json