SQL Monthly Pivot Query Generator Using Conditional Aggregation

SELECT
    item_code,
    SUM(CASE WHEN MONTH(transaction_date) = 1  THEN quantity ELSE 0 END) AS m01_jan,
    SUM(CASE WHEN MONTH(transaction_date) = 2  THEN quantity ELSE 0 END) AS m02_feb,
    SUM(CASE WHEN MONTH(transaction_date) = 3  THEN quantity ELSE 0 END) AS m03_mar,
    SUM(CASE WHEN MONTH(transaction_date) = 4  THEN quantity ELSE 0 END) AS m04_apr,
    SUM(CASE WHEN MONTH(transaction_date) = 5  THEN quantity ELSE 0 END) AS m05_may,
    SUM(CASE WHEN MONTH(transaction_date) = 6  THEN quantity ELSE 0 END) AS m06_jun,
    SUM(CASE WHEN MONTH(transaction_date) = 7  THEN quantity ELSE 0 END) AS m07_jul,
    SUM(CASE WHEN MONTH(transaction_date) = 8  THEN quantity ELSE 0 END) AS m08_aug,
    SUM(CASE WHEN MONTH(transaction_date) = 9  THEN quantity ELSE 0 END) AS m09_sep,
    SUM(CASE WHEN MONTH(transaction_date) = 10 THEN quantity ELSE 0 END) AS m10_oct,
    SUM(CASE WHEN MONTH(transaction_date) = 11 THEN quantity ELSE 0 END) AS m11_nov,
    SUM(CASE WHEN MONTH(transaction_date) = 12 THEN quantity ELSE 0 END) AS m12_dec,
    SUM(quantity) AS period_total
FROM dbo.inventory_transactions
WHERE transaction_date >= '2025-01-01'
  AND transaction_date <  '2026-01-01'
  AND transaction_type = 'ISSUE'
GROUP BY item_code
ORDER BY period_total DESC;

WITH monthly AS (
    SELECT
        item_code AS grp,
        YEAR(transaction_date) AS yr,
        MONTH(transaction_date) AS mth,
        SUM(quantity) AS month_value
    FROM dbo.inventory_transactions
    WHERE transaction_date >= '2025-01-01'
      AND transaction_date <  '2026-01-01'
      AND transaction_type = 'ISSUE'
    GROUP BY item_code, YEAR(transaction_date), MONTH(transaction_date)
)
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;

SELECT
    item_code,
    YEAR(transaction_date) AS yr,
    MONTH(transaction_date) AS mth,
    SUM(quantity) AS month_value
FROM dbo.inventory_transactions
WHERE transaction_date >= '2025-01-01'
  AND transaction_date <  '2026-01-01'
  AND transaction_type = 'ISSUE'
GROUP BY item_code, YEAR(transaction_date), MONTH(transaction_date)
ORDER BY item_code, yr, mth;