SQL Server Dynamic PIVOT Query

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.

Related Posts

2 Comments

  1. 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’,

  2. —-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

Leave a Reply

Your email address will not be published. Required fields are marked *