SQL Server：含2个分组变量与2个聚合计算的动态透视方案问询

Got it, let's tackle this problem head-on. The goal is to build a dynamic SQL query that automatically sums all numeric metric columns (like accounts, revenue, and any future ones) without hardcoding them, while grouping by State, Account Type, and month.

Step-by-Step Explanation #

The core idea is to:

• Dynamically fetch all numeric metric columns from your table (excluding grouping/date columns)
• Build a SELECT clause that includes SUM() for each metric
• Group by the required dimensions plus the year/month part of your date column

Example Implementation (SQL Server) #

Assuming your table is named sales_data, with columns: State, AccountType, TransactionDate, accounts, revenue, and any other numeric metrics you might add later.

DECLARE @metricColumns NVARCHAR(MAX);
DECLARE @dynamicSql NVARCHAR(MAX);

-- 1. Fetch all numeric metric columns (exclude grouping/date columns)
SELECT @metricColumns = STRING_AGG(
    CONCAT('SUM(', QUOTENAME(column_name), ') AS ', QUOTENAME(column_name)),
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'sales_data'
  AND COLUMN_NAME NOT IN ('State', 'AccountType', 'TransactionDate')
  AND DATA_TYPE IN ('int', 'decimal', 'numeric', 'float', 'money'); -- Include all numeric types relevant to your data

-- 2. Build the full dynamic SQL query
SET @dynamicSql = N'
SELECT
    State,
    AccountType,
    DATEFROMPARTS(YEAR(TransactionDate), MONTH(TransactionDate), 1) AS Month_Start,
    ' + @metricColumns + '
FROM sales_data
GROUP BY
    State,
    AccountType,
    YEAR(TransactionDate),
    MONTH(TransactionDate)
ORDER BY
    Month_Start,
    State,
    AccountType;
';

-- Optional: Print the generated SQL to verify before execution
-- PRINT @dynamicSql;

-- 3. Execute the dynamic query
EXEC sp_executesql @dynamicSql;

Adjustments for Other Databases #

• PostgreSQL: Replace DATEFROMPARTS with DATE_TRUNC('month', TransactionDate) AS Month_Start, and use STRING_AGG similarly. Execute with EXECUTE @dynamicSql;
• MySQL: Use GROUP_CONCAT instead of STRING_AGG, and DATE_FORMAT(TransactionDate, '%Y-%m-01') AS Month_Start. Execute with PREPARE stmt FROM @dynamicSql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Key Notes #

• Safety: QUOTENAME (or database-specific equivalents) ensures your query handles column names with spaces or special characters safely.
• Auto-Updates: If you add new numeric metric columns to your table later, this query will automatically include them in the aggregation without any code changes.
• Validation: Always print the generated @dynamicSql first (uncomment the PRINT line) to verify it includes the correct columns before executing.

内容的提问来源于stack exchange，提问作者Matt Reichenbach