Static and Dynamic Pivot Query in SQL Server

This post explains static and dynamic pivot query in SQL Server

Introduction

 

PIVOT clause is used to generate cross tab outputs in SQL Server. We put unique data values from a column in the PIVOT clause to render them as multiple columns in aggregation with other columns required in the output.

 

With the help of the PIVOT clause, we can transpose the distinct values of a column into multiple columns. However, the syntax of the PIVOT clause requires these distinct values to be known at the query design time.

 

We have two tables below, which store the details of student marks and their exam type.

 

         

 

 

Static Pivot Query

 

This kind of query can be considered a static pivot query. We can hard code these distinct values in the PIVOT clause only if these values are fixed and static in nature.

SELECT * FROM
 (
    SELECT StudentName, SubMarks, Subjects +'/'+ Exam AS Subjects
    FROM StudentMark
) x
PIVOT
(
    SUM(SubMarks)
    FOR Subjects in ([English/1-Mid], [English/2-Mid], [Maths/1-Mid],
    [Maths/2-Mid], [Tamil/1-Mid], [Tamil/2-Mid])
) p

 

 Result for the above-mentioned query:

 

 

 

Dynamic Pivot Query

  

However, when it comes to pivot uncertain values from a column, we wouldn't be able to cater to these values in the PIVOT clause at query design time. Also, SQL Server does not provide any built-in functionality which can be used in this scenario.

 

It facilitates the power of dynamic SQL which can generate and execute T-SQL commands dynamically, this technique can be used to generate and execute dynamic PIVOT queries. We can execute the dynamic PIVOT query with the help of EXECUTE or SP_ExecuteSQL command.

 

The steps we’ll take to generate the dynamic pivot table are:

 

Create a column list

Construct a Pivot Table as SQL Statement

Execute the statement

 

CREATE PROCEDURE DBO.USP_StudentReport
(
    @ExamType VARCHAR(20) = ''
)
AS

BEGIN

    SET NOCOUNT ON;

    --Parameter will hold the Pivoted Column values
    DECLARE @Cols AS NVARCHAR(MAX)

    --Parameter will hold the dynamically created SQL script
    DECLARE @Query  AS NVARCHAR(MAX)

    SELECT DISTINCT Subjects INTO #TmpSubject FROM StudentMark WITH(NOLOCK)

    SELECT @Cols = STUFF
    (
        (
            SELECT DISTINCT ',' + QUOTENAME(Subjects +'/'+ ExamName)
                FROM ExamTable WITH(NOLOCK), #TmpSubject
                WHERE ExamType = @ExamType
                GROUP BY ExamName,Subjects
                FOR XML PATH(''), Type
        ).value('.', 'NVARCHAR(MAX)'
    ),1,1,'')

    SET @Query = N'SELECT StudentName, ' + @Cols + N' FROM
        (
            SELECT StudentName, SubMarks, Subjects +''/''+ Exam as Subjects
            FROM StudentMark WITH(NOLOCK)
        ) x
        PIVOT
        (
            SUM(SubMarks)
            FOR Subjects IN (' + @Cols + N')
        ) p '

    EXEC SP_EXECUTESQL @Query

END
GO

 

If we execute the SP - EXEC USP_StudentReport 'Quarterly' we get the result:

 

 

 

 

Execute the SP with changing the exam type parameter value - EXEC USP_StudentReport 'Halfly' we get the result: