By: Eli Leiba | Comments (4) | Related: > TSQL
Problem
The PIVOT operator (or "cross-tab" operation) is a very handy tool in SQL Server, but its main limitation is that you often don't know the values that you need to pivot on. As a result, the operation is rarely used in programs and often in ad-hoc reports. This problem was discussed in a previous tip written by Aaron Bertrand that shows how to create a dynamic pivot query.
In this tip, we will handle the dynamic PIVOT issue a little differently. This will be accomplished by creating SQL Server stored procedure that accepts all inputs needed for the PIVOT query and executes the query like a "black-box".
Solution
My solution involves creating a T-SQL stored procedure in the SQL Server user's application database, called dbo.usp_Dyna_Pivot that accepts five string parameters as follows:
- @unknownValsCol - The column that the PIVOT operator uses in the FOR clause (in my example it is the year_study column).
- @objNameToPivot - The name of the table (or view) that the PIVOT operator uses (in my example it is the Student Grades table).
- @aggFuncOfPivot - The name of the aggregate function used (SUM, MIN, MAX, AVG… in my example it is AVG).
- @aggColOfPivot - The name of the column that is the parameter for the aggregate function of parameter 3 (in my example it is the grade column).
- @leadColPivot - The column that is used in the ORDER BY clause and the first column in the SELECT clause (in my example it is the courseName column).
Here is the stored procedure logic:
- The procedure uses a table variable and a string variable to hold the dynamic SQL statement and another string variable to hold the list of the distinct unknown values to PIVOT on.
- The procedure is constructed using dynamic T-SQL that makes a SELECT DISTINCT statement that finds the list of the distinct @unknownValsCol column values
- An EXEC dynamic T-SQL statement finds and creates a concatenated string of these values from the result of step 2.
- A CONCAT function constructs the string containing the PIVOT query with all the needed components for the query including the string from step 3.
- The string from step 4 is dynamically executed by using an EXEC statement.
SQL Server Stored Procedure to Create a Dynamic PIVOT Operation
-- ==================================================================================== -- Author: Eli Leiba -- Create date: 06-Nov-2019 -- Description: A procedure that uses dynamic SQL for a PIVOT operation -- ==================================================================================== CREATE PROCEDURE dbo.usp_Dyna_Pivot ( @unknownValsCol NVARCHAR (100), @objNameToPivot NVARCHAR (100), @aggFuncOfPivot NVARCHAR (3), @aggColOfPivot NVARCHAR (100), @leadColPivot NVARCHAR (100)) AS BEGIN DECLARE @columns NVARCHAR (2000), @tsql NVARCHAR (2000) DECLARE @distinctVals TABLE (val NVARCHAR (50)) SET NOCOUNT ON SET @columns = N''; SET @tsql = CONCAT ('SELECT DISTINCT ', @unknownValsCol,' FROM ',@objNameToPivot) INSERT @distinctVals EXEC (@tsql) SELECT @columns += CONCAT ('[', Val,']',',') FROM @distinctVals SET @columns = LEFT (@columns, LEN (@columns) - 1) SET @tsql = CONCAT ( 'SELECT ', @leadColPivot, ',', @columns,' FROM ',' ( SELECT ',@leadColPivot,',', @aggColOfPivot,',', @unknownValsCol, ' FROM ', @objNameToPivot, ') as t ', ' PIVOT (', @aggFuncOfPivot, '(', @aggColOfPivot, ')',' FOR ', @unknownValsCol, ' IN (', @columns,')) as pvt ',' ORDER BY ', @leadColPivot) EXEC (@tsql) SET NOCOUNT OFF END GO
Table Creation and Data Script
CREATE TABLE [dbo].[StudentGrades] ([studentName] [varchar](40) NULL, [courseName] [varchar](40) NULL, [year_study] [int] NULL, [Grade] [int] NULL) ON [PRIMARY] GO INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'oracle', 2017, 90) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'oracle', 2018, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'oracle', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'oracle', 2017, 95) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'oracle', 2018, 96) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'oracle', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r ', N'oracle', 2017, 95) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'oracle', 2018, 96) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'oracle', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'sql', 2017, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'sql', 2018, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'adir s', N'sql', 2019, 100) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'sql', 2017, 99) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'sql', 2018, 89) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'anat a', N'sql', 2019, 90) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r ', N'sql', 2017, 76) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'sql', 2018, 80) INSERT [dbo].[StudentGrades] ([studentName], [courseName], [year_study], [grade]) VALUES (N'ofir r', N'sql', 2019, 100) GO
Sample Execution
Execute the procedure in your application's database as follows:
EXEC dbo.usp_Dyna_Pivot @unknownValsCol = 'year_study', -- get list of unique values @objNameToPivot = 'StudentGrades', -- table that holds data @aggFuncOfPivot = 'AVG', -- type of operation to perform @aggColOfPivot = 'grade', -- column value for pivot operation @leadColPivot = 'courseName' -- order results by column GO
Sample Results:
Next Steps
- You can create and compile this stored procedure in your application database and use it as a simple T-SQL tool for executing dynamically the PIVOT operator.
- The procedure was tested with SQL Server 2014 and 2017, but should work for all versions from 2005 and later.
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips