By utilizing a Dynamic PIVOT Query in SQL Server, it permits you to convert rows into columns without explicitly defining the column names. This approach is particularly beneficial in situations where the column names are variable and shift as time progresses. In this article, we will delve into the various methods through which we can formulate a Dynamic PIVOT Query within SQL Server.
Contents:
- What is a Dynamic PIVOT Query?
- Why is it necessary to create a Dynamic PIVOT Query in SQL Server?
- Benefits of establishing a Dynamic PIVOT Query in SQL Server
- Ways to Create a Dynamic PIVOT Query in SQL Server
- Alternate Methods for Establishing Dynamic PIVOT Query
- Performance Analysis of Each Approach
- Practical Examples
- Optimal Practices
- Summary
- How to Formulate a Dynamic PIVOT Query in SQL Server – FAQs
What is a Dynamic PIVOT Query?
A Dynamic PIVOT Query is an operation in SQL Server that enables the dynamic creation of column names at execution time based on the content of the table, rather than relying on hardcoded names. The PIVOT query can be constructed utilizing ‘EXEC sp_executesql’. When the column values are variable, the Dynamic PIVOT Query proves advantageous for data reporting and transformation.
Why is it necessary to create a Dynamic PIVOT Query in SQL Server?
A Dynamic PIVOT Query is necessary in SQL Server when the column values are not static and evolve over time. By avoiding the fixation of column names, it allows rows to be dynamically converted into columns, enhancing the query’s adaptability for varied datasets. Utilizing Dynamic SQL helps the query adjust automatically to the newly introduced data without manual intervention.
Benefits of establishing a Dynamic PIVOT Query in SQL Server
- Effective Reporting: Valuable for crafting dashboards and reports where column names are subject to change over time.
- High Scalability: Efficiently manages substantial datasets without the need for frequent updates.
- Lowers Maintenance Effort: The automatic updates of new data diminish the manual workload required.
- High Flexibility: It adjusts to fluctuating data automatically, negating the need for manual SQL modifications.
Ways to Create a Dynamic PIVOT Query in SQL Server
A Dynamic PIVOT query can be created in SQL Server through several methodologies depending on the version and specific use cases, such as using STUFF and STRING_AGG.
Prior to discussing the methods, let us establish an Employee Attendance table and insert some sample data that we will utilize in the upcoming sections.
CREATE TABLE Em_Atd (
Em_ID INT,
Atd_Mon VARCHAR(20),
Atd_dy INT
);
INSERT INTO Em_Atd (Em_ID, Atd_Mon, Atd_dy) VALUES
(101, 'January', 20),
(101, 'February', 18),
(102, 'January', 22),
(102, 'February', 21),
(103, 'January', 19),
(103, 'March', 20);
Select * from Em_Atd;
The table appears as shown after its creation and the insertion of values.
Method 1: Utilizing STUFF and FOR XML PATH in SQL Server
Within a conventional PIVOT query, dynamic generation of column names is not feasible; thus, we leverage the STUFF and FOR XML PATH functions to construct the column lists dynamically.
Syntax:
SET QUOTED_IDENTIFIER ON;
DECLARE @Co NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SELECT @Co = STUFF((
SELECT DISTINCT ',' + QUOTENAME(co_nam)
FROM Tab_nam
EXEC sp_executesql @SQL;
Example:
SET QUOTED_IDENTIFIER ON;
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STUFF((
SELECT DISTINCT ',' + QUOTENAME(Atd_Mon)
FROM Em_Atd
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @sql = '
SELECT * FROM (
SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS SourceTable
PIVOT (
MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotResult;
';
EXEC sp_executesql @sql;
Output:
Description: This method produces a list of distinct months delineated by commas through concatenation with FOR XML PATH. The STUFF function will eliminate the initial comma from the sequence to ensure it is formatted correctly for inclusion in the dynamic column list of the PIVOT statement.
Technique 2: Employing STRING_AGG in SQL Server
The STRING_AGG function facilitates dynamic concatenation of column values into lists separated by commas.
Structure:
SET QUOTED_IDENTIFIER ON;
SELECT @Columns = STRING_AGG(QUOTENAME(CoPt), '', '')
FROM (SELECT DISTINCT CoPt FROM Tab_nam) AS List;
EXEC sp_executesql @SQL;
Illustration:
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), '','')
FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS DistinctMonths;
SET @sql = ''
SELECT * FROM (
SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS SourceTable
PIVOT (
MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;
Result:
Description: This segment creates a unique list of months separated by commas using QUOTENAME and STRING_AGG. These month identifiers are subsequently fed into the dynamic PIVOT, producing headers such as [January], [February], etc.
Technique 3: Utilizing Temp Table or Table Variable in SQL Server
Before executing the PIVOT action, the Temp Table (#TempTable) or Table Variable (@TableVariable) can be utilized to preserve the intermediate results.
Structure:
SET QUO_IDNT ON;
SET @SQL = ''
SELECT * FROM (
SELECT CoA FROM #TempTable
) AS SrTab
PIVOT (
AggregateFunction(C3) FOR C2 IN (' + @Cos + ')
) AS PivotTable;
';
EXEC sp_executesql @SQL;
DROP TABLE #TempTable;
Illustration:
DECLARE @cols NVARCHAR(MAX) = ''', @sql NVARCHAR(MAX);
CREATE TABLE #Months (MonthName VARCHAR(50));
INSERT INTO #Months
SELECT DISTINCT Atd_Mon FROM Em_Atd;
SELECT @cols = STRING_AGG(QUOTENAME(MonthName), '',') FROM #Months;
SET @sql = ''
SELECT * FROM (
SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS SourceTable
PIVOT (
MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;
DROP TABLE #Months;
Result:
Description: Here, unique month values are stored in a temporary table, followed by invoking STRING_AGG to generate the pivot column list. This dynamic list transposes Atd_dy values into monthly columns for each employee.
Technique 4: Employing CTE in SQL Server
The CTE serves as a temporary result set defined within the context of the WITH statement.
Structure:
SET QUOTED_IDENTIFIER ON;
SET @SQL = ''
WITH CTE_Name AS (
SELECT CoB, CoA
FROM SrTab
WHERE Condt
)
EXEC sp_executesql @SQL;
Illustration:
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
WITH MonthList AS (
SELECT DISTINCT Atd_Mon FROM Em_Atd
)
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), '','') FROM MonthList;
SET @sql = ''
WITH BaseData AS (
SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
)
SELECT * FROM BaseData
PIVOT (
MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;
Result:
Description: In this case, the CTE selects distinct month names to create a consolidated column list for pivoting. This collection generates the columns dynamically for converting rows into a pivot table representation.
Alternative Techniques for Developing Dynamic PIVOT Query
There exist several other methods for crafting a Dynamic PIVOT query, including utilizing EXEC() and leveraging sp_executesql.
Technique 1: Utilizing EXEC() for Dynamic PIVOT
The EXEC() function in SQL Server offers an alternative means through which we can dynamically formulate SQL Statements. In this strategy, a PIVOT query can be assembled as a string and subsequently executed.
Structure:
DECLARE @Cmn NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @Cmn = ''';
SELECT @Cmn = @Cmn + QUOTENAME(Colname) + '', '
SET @Cmn = LEFT(@Cmn, LEN(@Cmn) - 1);
EXEC(@SQL);
Illustration:
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), '','') FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS M;
SET @query = ''
SELECT Em_ID, '' + @cols + ''
FROM (
SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS src
PIVOT (
MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + '')
) AS pvt;';
EXEC(@query);
Result:
Description: Within this scenario, the STRING_AGG(QUOTENAME(Atd_Mon), ‘’, ‘) retrieves unique region identifiers, and the constructed query is executed using EXEC().
Technique 2: Leveraging sp_executesql for Dynamic PIVOT
In this approach, sp_executesql is a system-stored procedure in SQL Server tasked with executing dynamically generated statements.
“`SQL Queries.
Structure:
DECLARE @Columns NVARCHAR(MAX);
PIVOT (
SUM(AggregateColumn) FOR PivotColumn IN (' + @Columns + ')
) AS PivotTable;
EXEC sp_executesql @SQL;
Illustration:
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), ',') FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS M;
SET @query = '
SELECT Em_ID, ' + @cols + '
FROM (
SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS src
PIVOT (
MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS pvt;';
EXEC sp_executesql @query;
Result:
Clarification: In this context, STRING_AGG(QUOTENAME(Atd_Mon), ‘, ‘) retrieves unique Region values to ensure proper column identifiers.
Performance Evaluation of Each Approach
| Technique | Application Scenario | Advantages | Disadvantages |
| Utilizing STUFF and FOR XML PATH | Employed when generating a CSV from table data | Effectively manages dynamic column creation | Performance is diminished due to FOR XML PATH processing |
| Utilizing STRING_AGG | Helpful when needing to concatenate multiple row values efficiently | More straightforward and efficient than XML-based solutions | Not available in earlier SQL Server releases |
| Utilizing Temp Table | Applicable when intermediate storage is required prior to pivoting | Saves intermediate results for subsequent debugging | Increased memory consumption due to temporary storage |
| Utilizing CTE | Useful for conducting temporary computations | Easily integrated with other queries for improved structure | Performance may be inferior compared to the Temp Table |
Practical Illustrations
Healthcare Management: A medical institution aims to monitor patient service counts by physician department and month.
Illustration:
CREATE TABLE DptVisit (
VtMnt VARCHAR(10),
Dt_name NVARCHAR(50),
Pt_Cnt INT
);
INSERT INTO DptVisit (VtMnt, Dt_name, Pt_Cnt) VALUES
('Jan', 'Cardiology', 150),
('Jan', 'Neurology', 120),
('Feb', 'Cardiology', 170),
('Feb', 'Neurology', 130),
('Mar', 'Cardiology', 160),
('Mar', 'Orthopedics', 110);
SET QUOTED_IDENTIFIER ON;
DECLARE @DeptList NVARCHAR(MAX);
DECLARE @PivotQuery NVARCHAR(MAX);
SELECT @DeptList = STRING_AGG(QUOTENAME(Dt_name), ', ')
FROM (SELECT DISTINCT Dt_name FROM DptVisit) AS D;
SET @PivotQuery = N'
SELECT * FROM (
SELECT VtMnt, Dt_name, Pt_Cnt FROM DptVisit
) AS VisitData
PIVOT (
SUM(Pt_Cnt) FOR Dt_name IN (' + @DeptList + ')
) AS MonthlyVisits;';
EXEC sp_executesql @PivotQuery;
Result:
Clarification: This query pivots the monthly tally of patient visits by department and updates automatically as new departments are introduced.
Optimal Strategies
- To prevent SQL injection and ensure proper formatting of column identifiers, utilize QUOTENAME().
- As sp_executesql supports parameterized queries, which bolster both security and performance, it is preferred over EXEC().
- For simplified dynamic column list creation, use STRING_AGG() (available from SQL Server 2017+); for earlier versions, employ STUFF() alongside FOR XML PATH.
- To identify problems, always PRINT the dynamic SQL before its execution.
Summary
By implementing QUOTENAME() for security, sp_executesql for execution, and STRING_AGG() or STUFF() for the generation of column lists, a flexible, secure, and effective method can be ensured. Reliability is further enhanced by integrating error management (TRY…CATCH), indexing to improve performance, and debugging (PRINT). Throughout this blog, you have acquired insights into various methodologies for creating Dynamic PIVOT queries in SQL Server.
Enhance your abilities by signing up for our SQL Training Course today, gaining practical experience. Furthermore, prepare for job interviews with our SQL interview questions curated by industry professionals.
How to Construct a Dynamic PIVOT Query in SQL Server – FAQs
Some of the common functions include QUOTENAME() for safety, STUFF() with FOR XML PATH, STRING_AGG(), and sp_executesql for execution.
To mitigate injection risks and avoid special characters, always use QUOTENAME() when constructing column names dynamically.
Yes, however, STRING_AGG() is accessible from SQL Server 2017+, while STUFF() with FOR XML PATH is necessary for earlier SQL Server versions
Before running the query, utilize PRINT @SQL to validate the constructed SQL statement.
Common functions include SUM(), MAX(), MIN(), and COUNT(), depending on the specific requirements.
The article How to Construct a Dynamic PIVOT Query in SQL Server first appeared on Intellipaat Blog.