Answer

Historically, the tasks of dynamic reports with a variable number of groupings were not anticipated by the SQL standard — it is designed for statically described queries with a clearly defined result schema. With the advent of BI platforms and interactive dashboards, programmers began looking for ways to construct SQL queries "on the fly" to generate complex multiple data slices based on user requests.

Problem — SQL cannot be programmed with regular if/else logic inside SELECT — the number of columns, the fields themselves, and even GROUP BY are only determined at the query compilation stage. If a user wants a slice from several arbitrary fields, it is necessary to dynamically assemble the text of the query and execute it via EXECUTE/Dynamic SQL.

Solution:

Generate SQL code in the external application and then call it via EXEC/EXECUTE. In some cases, simple tasks use CASE and hard templates, but for flexibility, dynamic SQL is always resorted to:

Code example (Pseudocode):

-- On the application side (e.g., Python)
groups = ['region', 'channel', 'month']
columns = [f'SUM({col}) AS {col}_sum' for col in selected_metrics]
group_by = ', '.join(groups)
selects = ', '.join(groups + columns)

query = f'SELECT {selects} FROM sales GROUP BY {group_by}'
-- Then sending this query through the application/sql-interface

In a DBMS with EXECUTE support:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT ' + @selects + ' FROM sales GROUP BY ' + @group_by + ';';
EXEC sp_executesql @sql;

Key features:

Requires validation of variables to avoid SQL Injection
Almost always associated with application architecture, templates for SELECT/GROUP BY are implemented outside of SQL
With a large number of options, a smart query builder or templater is required

Tricky Questions.

Can dynamic changes in the number of columns (pivot/unpivot) be implemented only through standard SELECT without dynamic SQL?

No — only pre-known values can be "unfolded" through CASE/DECODE, but an unknown number of columns can only be achieved dynamically.

Does your dynamic SQL logic guarantee protection from injections if you use string concatenation?

No, manual string concatenation poses a constant risk of SQL Injection. It is necessary to validate the list of fields/groupings through whitelisting, avoid user fragments without checks, and preferably use parameters where possible.

Can GROUP BY accept a list of columns through a variable?

Standard SQL does not support passing a list of fields in GROUP BY through a variable/parameter. It is necessary to dynamically form the text of the query — simply using a variable in the GROUP BY list is not allowed.

Common Mistakes and Anti-Patterns

Unverified user field names — injections
Hardcoded field list — lack of flexibility
Failure to consider possible null values in groups

Real-Life Example

Negative Case

A BI engineer allowed the user to pass field names for the report directly from the application interface — without filtering them through whitelisting. As a result, during the injection test, the production table was "dropped" due to malicious code injected in the field name.

Pros:

Maximum flexibility in report construction

Cons:

Security suffers due to unvalidated variables

Positive Case

The engineer implemented strict validation of names — the user could only choose from allowed columns (from config/metadata), dynamic SQL was only assembled based on whitelisting, and there were no paths for injection.