Exploring dynamic SQL and dynamic pivot in SQL SELECT

18 Sep 2023

In SQL, dynamic SQL refers to the ability to construct and execute SQL statements at runtime. It allows you to create flexible queries that can be customized based on user input or other programmatic conditions. Dynamic SQL is often used when the structure of the SQL statement needs to be determined dynamically.

Dynamic pivot, on the other hand, allows you to pivot data at runtime based on dynamic column values. It is particularly useful when you have unknown or changing column values that need to be transformed into columns in the result set.

Dynamic SQL

Dynamic SQL involves the construction and execution of SQL statements using strings. It gives you the ability to dynamically modify queries based on specific conditions. Here’s a simple example of dynamic SQL using the SQL Server syntax:

DECLARE @tableName VARCHAR(50) = 'employees';
DECLARE @columnList VARCHAR(MAX) = 'employee_id, first_name, last_name';
DECLARE @sqlStatement VARCHAR(MAX);

SET @sqlStatement = 'SELECT ' + @columnList + ' FROM ' + @tableName;

EXEC(@sqlStatement);

In this example, the @tableName and @columnList variables are used to dynamically construct the SQL statement. The @sqlStatement variable holds the final query and is executed using the EXEC() function. This way, you can easily customize the table name and column list based on your requirements.

Using dynamic SQL allows you to build queries that are not limited by static statements, providing more flexibility and adaptability to handle different scenarios.

Dynamic Pivot

Dynamic pivot, as the name suggests, allows you to pivot data dynamically at runtime. It enables you to convert row values into columns in the result set, even when the column values are not known beforehand. Here’s an example using SQL Server:

DECLARE @pivotColumn VARCHAR(50) = 'Month';
DECLARE @sqlStatement VARCHAR(MAX) = '
    SELECT * FROM (
        SELECT Product, ' + @pivotColumn + ', Quantity
        FROM Sales
    ) AS src
    PIVOT (
        SUM(Quantity)
        FOR ' + @pivotColumn + ' IN ([January], [February], [March], [April])
    ) AS piv';

EXEC(@sqlStatement);

In this example, the @pivotColumn variable is used to specify the column that needs to be pivoted dynamically. The @sqlStatement variable holds the dynamic SQL query, which uses the PIVOT keyword to transform the data. In this case, the pivot column is set to 'Month', and the resulting columns are hardcoded to specific months. However, you can modify the query to generate the column names dynamically based on your data.

Dynamic pivot allows you to handle scenarios where the column values may change or are not known in advance. It provides a convenient way to transform your data into a more meaningful structure.

Summary

Dynamic SQL and dynamic pivot are powerful techniques in SQL that allow you to construct and execute SQL statements at runtime, providing flexibility and adaptability to handle varying scenarios. By using dynamic SQL, you can dynamically modify queries based on specific conditions or user input. With dynamic pivot, you can transform row values into columns dynamically, even when the column values are not known in advance. These techniques offer greater control and customization in SQL SELECT statements.