Answer.

Analytical reports with dynamic filtering, grouping, and aggregation by multiple parameters are the core of BI and Data Warehouse applications. Previously, these tasks were solved exclusively through static queries, but with the increasing requirements, there arose a need for dynamic SQL construction on the fly: the number of fields and groupings is determined by the user or the application.

Problem — the inability to deterministically define all possible field variants for grouping and aggregation in advance. It is necessary to build queries dynamically, which can lead to errors and performance degradation if queries are poorly planned and inappropriate indexes are used.

Solution — implement query construction using dynamic SQL (for example, through EXEC or sp_executesql in T-SQL), forming the list of fields for SELECT, GROUP BY, and relevant aggregate functions from user parameters. At the same time, careful escaping of field names and protection against SQL injection is essential. Example for SQL Server:

DECLARE @select_fields nvarchar(max) = N'customer_id, year';
DECLARE @agg_fields nvarchar(max) = N'SUM(amount) AS total, COUNT(*) AS row_count';
DECLARE @group_by nvarchar(max) = N'customer_id, year';

DECLARE @sql nvarchar(max) = 
    N'SELECT ' + @select_fields + ', ' + @agg_fields + N' FROM sales WHERE sale_date >= @start AND sale_date <= @end GROUP BY ' + @group_by;

EXEC sp_executesql @sql, N'@start DATE, @end DATE', @start='2023-01-01', @end='2023-12-31';

Key features:

Flexible query structure formation based on user parameters.
Use of safe dynamic SQL with mandatory parameterization.
Understanding the necessity of indexing for potential filtering and grouping fields.

Tricky Questions.

Can a single universal query with CASE be created in advance for all possible groupings instead of using dynamic SQL?

No, CASE allows calculations in SELECT but does not facilitate defining GROUP BY for dynamically determined fields — they still need to be explicitly specified. The static approach is too limited for live BI analytics.

Should SQL query parameters be used when dynamically constructing WHERE, or can values be inserted directly into the string text?

Always use parameters (for example, through sp_executesql), otherwise, you risk SQL injection vulnerabilities, and protected values (such as apostrophes in strings) will be improperly handled.

Will an index on all possible combinations of fields speed up any reports?

No, creating a composite index on dozens of fields increases the index size and slows down insert/update speed, while only a strictly limited number of scenarios benefit from it. Index only the actually needed fields/groups.

Common Mistakes and Anti-Patterns

Dynamic string concatenation without parameterization (SQL Injection)
Lack of selectivity analysis when designing indexes
Forming GROUP BY without considering the actual table composition

Real-Life Example

Negative Case

In an old BI report, 30 grouping variants were "hard" coded with if-else in the application. Every new parameter required updating the source code and index scheme. Pros:

Simplicity of understanding
Quickly implementable with a small number of fields

Cons:

No flexibility
Difficult to extend and maintain

Positive Case

Dynamic SQL was used to form select/group by on the fly, SQL code in a separate module, parameters strictly escaped. Adding new fields was simply a matter of adjusting a reference table, indexes were added based on filtering frequency. Pros:

Flexibility, scalability
Security