SQL Server

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.
  1. SELECT * FROM   
  2.  (  
  3.     SELECT StudentName, SubMarks, Subjects +'/'+ Exam AS Subjects  
  4.     FROM StudentMark  
  5. ) x  
  6. PIVOT   
  7. (  
  8.     SUM(SubMarks)  
  9.     FOR Subjects in ([English/1-Mid], [English/2-Mid], [Maths/1-Mid],   
  10.     [Maths/2-Mid], [Tamil/1-Mid], [Tamil/2-Mid])  
  11. ) 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
 
  1. CREATE PROCEDURE DBO.USP_StudentReport  
  2. (  
  3.     @ExamType VARCHAR(20) = ''  
  4. )  
  5. AS  
  6.   
  7. BEGIN  
  8.       
  9.     SET NOCOUNT ON;  
  10.       
  11.     --Parameter will hold the Pivoted Column values  
  12.     DECLARE @Cols AS NVARCHAR(MAX)  
  13.       
  14.     --Parameter will hold the dynamically created SQL script  
  15.     DECLARE @Query  AS NVARCHAR(MAX)   
  16.       
  17.     SELECT DISTINCT Subjects INTO #TmpSubject FROM StudentMark WITH(NOLOCK)  
  18.       
  19.     SELECT @Cols = STUFF  
  20.     (  
  21.         (  
  22.             SELECT DISTINCT ',' + QUOTENAME(Subjects +'/'+ ExamName)   
  23.                 FROM ExamTable WITH(NOLOCK), #TmpSubject  
  24.                 WHERE ExamType = @ExamType  
  25.                 GROUP BY ExamName,Subjects  
  26.                 FOR XML PATH(''), Type  
  27.         ).value('.', 'NVARCHAR(MAX)'  
  28.     ),1,1,'')  
  29.   
  30.     SET @Query = N'SELECT StudentName, ' + @Cols + N' FROM   
  31.         (  
  32.             SELECT StudentName, SubMarks, Subjects +''/''+ Exam as Subjects  
  33.             FROM StudentMark WITH(NOLOCK)  
  34.         ) x  
  35.         PIVOT   
  36.         (  
  37.             SUM(SubMarks)  
  38.             FOR Subjects IN (' + @Cols + N')  
  39.         ) p '  
  40.   
  41.     EXEC SP_EXECUTESQL @Query  
  42.   
  43. END  
  44. 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:
 
 
 
 
 
Ebook Download
View all
Learn
View all
Membership not found