SQL Server Dynamic PIVOT Query

By: Eli Leiba | Updated: 2019-12-04 | 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

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-12-04

Comments For This Article

Thursday, October 3, 2024 - 6:40:29 AM - Vakili

Adding Where

DROP PROCEDURE _pivot
go
create PROCEDURE [dbo].[_PIVOT]
(
@STATIC_COLUMN VARCHAR(255),
@PIVOT_COLUMN VARCHAR(255),
@VALUE_COLUMN VARCHAR(255),
@TABLE VARCHAR(255),
@Where VARCHAR(255)=Null,
@AGGREGATE VARCHAR(20) = null
)

AS

BEGIN

SET NOCOUNT ON;
declare @AVAIABLE_TO_PIVOT NVARCHAR(MAX),
@SQLSTRING NVARCHAR(MAX),
@PIVOT_SQL_STRING NVARCHAR(MAX),
@TEMPVARCOLUMNS NVARCHAR(MAX),
@TABLESQL NVARCHAR(MAX)

if isnull(@AGGREGATE,'') = ''
begin
SET @AGGREGATE = 'MAX'
end

SET @PIVOT_SQL_STRING = 'SELECT top 1 STUFF((SELECT distinct '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']'' AS VARCHAR(50)) [text()]
FROM '+@TABLE+'
WHERE ISNULL('+@PIVOT_COLUMN+','''') <> ''''
FOR XML PATH(''''), TYPE)
.value(''.'',''NVARCHAR(MAX)''),1,2,'' '') as PIVOT_VALUES
from '+@TABLE+' ma
ORDER BY ' + @PIVOT_COLUMN + ''

declare @TAB AS TABLE(COL NVARCHAR(MAX) )

INSERT INTO @TAB EXEC SP_EXECUTESQL @PIVOT_SQL_STRING, @AVAIABLE_TO_PIVOT

SET @AVAIABLE_TO_PIVOT = (SELECT * FROM @TAB)

SET @TEMPVARCOLUMNS = (SELECT replace(@AVAIABLE_TO_PIVOT,',',' nvarchar(255) null,') + ' nvarchar(255) null')

SET @SQLSTRING = 'DECLARE @RETURN_TABLE TABLE ('+@STATIC_COLUMN+' NVARCHAR(255) NULL,'+@TEMPVARCOLUMNS+')
INSERT INTO @RETURN_TABLE('+@STATIC_COLUMN+','+@AVAIABLE_TO_PIVOT+')

select * from (
SELECT ' + @STATIC_COLUMN + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLE+
CASE WHEN NOT @Where IS NULL THEN ' WHere '+@wHERE
ELSE '' End
+

' ) a

PIVOT
(
'+@AGGREGATE+'('+@VALUE_COLUMN+')
FOR '+@PIVOT_COLUMN+' IN ('+@AVAIABLE_TO_PIVOT+')
) piv

SELECT * FROM @RETURN_TABLE '
PRINT @SQLSTRING

EXEC SP_EXECUTESQL @SQLSTRING

END
go
exec [dbo].[_PIVOT] 'MilkPeriod','Sex','BDate','Main',

Friday, October 21, 2022 - 5:08:13 AM - Jatin Baliyan

----Hi, a great example of dynamic stored procedure

ALTER PROCEDURE pro_web
@Table_Name NVARCHAR (100)
AS
BEGIN

DECLARE @DynamicSQL NVARCHAR(4000)

DECLARE @columns NVARCHAR (2000),
@tsql NVARCHAR (2000)
DECLARE @distinctVals TABLE (val NVARCHAR (2000))

SET NOCOUNT ON
SET @columns = N''
SET @tsql = CONCAT ('SELECT DISTINCT zip FROM ',@Table_Name)
INSERT @distinctVals EXEC (@tsql)

SELECT @columns += CONCAT ('[', Val,']',',')
FROM @distinctVals

SET @columns = LEFT (@columns, LEN (@columns) - 1)

SET @tsql = N'
SELECT * FROM ( SELECT zip,category,COUNT(category) AS cca FROM '+ @Table_Name+' GROUP BY category,zip) as t
PIVOT(
SUM([cca])
FOR [zip]
IN ('+ @columns+')) as pvt ORDER BY category'
EXEC (@tsql)
SET NOCOUNT OFF

END

EXEC pro_web @Table_Name=Backup_Peapod_10_12_2022

Wednesday, March 11, 2020 - 5:03:08 AM - Jeanneke

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

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