SQL Server Dynamic PIVOT Query


By:   |   Updated: 2019-12-04   |   Comments (2)   |   Related: More > T-SQL


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:

  1. 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.
  2. The procedure is constructed using dynamic T-SQL that makes a SELECT DISTINCT statement that finds the list of the distinct @unknownValsCol column values
  3. An EXEC dynamic T-SQL statement finds and creates a concatenated string of these values from the result of step 2.
  4. A CONCAT function constructs the string containing the PIVOT query with all the needed components for the query including the string from step 3.
  5. 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 data

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:

result set
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.


Last Updated: 2019-12-04


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips



Comments For This Article




Wednesday, March 11, 2020 - 5:03:08 AM - Jeanneke Back To Top (85053)

Hi,

Great procedure. Very helpful.

There's one thing I can't get done, sorting out the pivot columns.
The pivot column is of the size yyyymm type INT. It is sorted :
201912 - 201906 - 202012 - 201909.....

I'd like to have it this way:
201906 - 201907 - 201908 - 201909....

Is there a way to get this done?
Thanks.


Wednesday, December 4, 2019 - 9:35:42 AM - Scott Stanek Back To Top (83278)

I liked your code and will be leveraging it as I can, but I noticed a possible issue when I tried it on some of my data --then added a little something in the @tsql to wrap the aggregated field in an isnull replace with 0 before the aggregation happens so that a single null value in the data wouldn't invalidate the result cell. It renames it with the 'as' to keep the rest of the code working.

'isnull(',  @aggColOfPivot, ',0) as ',@aggColOfPivot,

-- ====================================================================================
-- Author:      Eli Leiba
-- Create date: 06-Nov-2019
-- Description: A procedure that uses dynamic SQL for a PIVOT operation 
-- ====================================================================================
/*
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
*/
Alter 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 (max),
      @tsql NVARCHAR (max)
   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,',',
       'isnull(',  @aggColOfPivot, ',0) as ',@aggColOfPivot,  ',',   @unknownValsCol,   ' FROM ',   @objNameToPivot,   ') as t ',
         ' PIVOT (', @aggFuncOfPivot,   '(', @aggColOfPivot,   ')',' FOR ',   @unknownValsCol,
         '   IN (', @columns,')) as pvt ',' ORDER BY ',   @leadColPivot)
   EXEC (@tsql)
   --select (@tsql)
   SET NOCOUNT OFF
END
GO


download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

SQL Server DROP TABLE IF EXISTS Examples














get free sql tips
agree to terms