Aggregate Functions In Dynamic Pivot Using Common Table Expression

This article explain about how to achieve add aggregate function in dynamic pivot in single table without create temporary tables.

Introduction

This article explains how to add an aggregate function in dynamic pivot in a single table without creating temporary tables.

What is pivot?

PIVOT and UNPIVOT relational operators change a table-valued expression into another table. Pivot is changing your normal row-based table to column based table.

What is CTE?

Specifies a temporarily named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

In this article, we insert data to student_Register table like Stud_id, Att_Date, Present, and get student registration data with total days and days present.

Create student_Register table

First create student_Register table with columns Stud_id, Att_Date, Present. This table is for inserting everyday student attendance records with respective student id and date. But we need a data for showing each day present with total days working and total days present for the student (Stud_id).

  1. use master  
  2.   
  3. IF OBJECT_ID (N'dbo.student_Register'IS NOT NULL  
  4. drop table [student_Register]  
  5.   
  6. create table [student_Register]  
  7. (  
  8. ID int not null unique identity(1,1),  
  9.   Stud_id int not null ,  
  10.    ATT_DATE varchar(50) not null,  
  11.      PRESENT int not null  
  12.   
  13. )  

Insert sample records to student_Register Table

  1. insert into student_Register  
  2. select 1, '01-08-2015', 1 union all  
  3. select 2, '01-08-2015', 0 union all  
  4. select 3, '01-08-2015', 0 union all  
  5. select 1, '02-08-2015', 0 union all  
  6. select 2, '02-08-2015', 0 union all  
  7. select 3, '02-08-2015', 1 union all  
  8. select 1, '03-08-2015', 1 union all  
  9. select 2, '03-08-2015', 0 union all  
  10. select 3, '03-08-2015', 0 union all  
  11. select 1, '04-08-2015', 0 union all  
  12. select 2, '04-08-2015', 0 union all  
  13. select 3, '04-08-2015', 1 UNION ALL  
  14. select 1, '05-08-2015', 1 union all  
  15. select 2, '05-08-2015', 0 union all  
  16. select 3, '05-08-2015', 0  
Create table and insert sample data
Figure 1: Create a table and insert the sample data

Query for GET Column (ATT_DATE) Value In single String

 Parse the unique comma separated string values of ATT_DATE using STUFF and XML PATH(''). We can use XmlPath('') to concatenate column data into a single row. Use ‘,’ to add value to the end of each column. QUOTENAME accepts a String as input and returns a string of a valid delimited identifier ’01-08-2015’ to [01-08-2015].

  1. DECLARE @cols AS NVARCHAR(MAX)  
  2. DECLARE  @query  AS NVARCHAR(MAX)  
  3. DECLARE  @CteWithAgFn  AS NVARCHAR(MAX)  
  4. DECLARE @SEPERATOR as VARCHAR(1)   
  5. DECLARE @SP  INT  
  6. DECLARE @VALUE VARCHAR(MAX)  
  7. DECLARE @INSTR VARCHAR(MAX)  
  8. DECLARE @ORGSTR VARCHAR(MAX)  
  9. DECLARE @COLSREV VARCHAR(MAX)  
  10.   
  11.   
  12. SET @SEPERATOR = ','  
  13. SET @COLSREV = ''  
  14. SET @ORGSTR = STUFF((SELECT distinct ',' + QUOTENAME([ATT_DATE])   
  15.             FROM dbo.student_Register   
  16.             FOR XML PATH(''), TYPE  
  17.             ).value('.''NVARCHAR(MAX)')   
  18.         ,1,1,'')  
  19.           
  20. SET @INSTR = ''+@ORGSTR +','  
ATT_DATE column  to single string
Figure 2:ATT_DATE column  to a single string

Insert aggregate function (min ()) to each column value using while loop

This is an important step. Here, we will add min() function to each date using PATINDEX, and while loop.

  • Step 1 - Looping date String (@INSTR) use patindex comma separated.
  • Step 2 - Take the first set of date string using LEFT(@INSTR , @SP – 1)
  • Step 3 - Add min() function to first step and save this first step in @COLSREV.
  • Step 4 - Remove first step use STUFF(), loop is continue until end date.
  1. ----------LOOP FOR INSERT MIN() FUNCTION IN TO THE INSTR  
  2. WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0   
  3. BEGIN  
  4.    SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)  --LENGTH OF FIRST STRING SET INCLUDE COMMA  
  5.     SELECT  @COLS = LEFT(@INSTR , @SP - 1)              -- TAKE FIRST SET  
  6.     SET @COLS =  'min('+@COLS+') AS '+@COLS+'';         --ADD FUN IN FIRST SET  
  7.    SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')   -- REMOVE FIRST SET STRING FROM @INSTR  
  8.    SET @COLSREV +=''+@COLS+','  
  9.   END ----LOOP END  
  10.   
  11.   set @COLSREV = STUFF(@COLSREV,LEN(@COLSREV),1,'')  
insert aggregate function to column value
Figure 3: insert aggregate function to a column value

Make dynamic pivot with aggregate functions

Insert date string with aggregate function (@COLSREV) to pivot, finally, we get single SQL command string is @query.

  1. set @query = ‘SELECT  Stud_id, ‘+@COLSREV+’   from(  select * from student_Register  )   
  2.     x   
  3.            pivot   
  4.            (  
  5.          min([PRESENT])  
  6.             
  7. for [ATT_DATE] in (‘+@ORGSTR+’)  
  8.    ) As p group by [Stud_id] ‘ 
Dynamic pivot
Figure 4: Dynamic pivot

Create Common Table Expression with dynamic pivot query

Create CTE for dynamic pivot query (@query) and select columns. We need to show Stud_id, Att_Dates ( from @ORGSTR), total days, days of the present.

  1.     set @CteWithAgFn  = 'with mycte  
  2.     As  
  3.     (  
  4.       
  5.     '+@query+'  
  6.   
  7.     ),  
  8.      mycte1  
  9.     As(  
  10.     SELECT Stud_id, COUNT(PRESENT) AS [TOTAL DAYS], SUM(PRESENT) AS [DAYS OF PRESENT] FROM student_Register GROUP BY Stud_id  
  11.     )  
  12.   
  13.     select mycte1.Stud_id, '+@ORGSTR+' , mycte1.[TOTAL DAYS], mycte1.[DAYS OF PRESENT] from mycte  
  14. left join mycte1 on mycte.Stud_id = mycte1.Stud_id'  

 SQL Server

Execute the dynamic query using sp_executesql

sp_executesql - Executes a SQL statement or batch that can be reused many times or one that has been built dynamically.

Here, we execute a full dynamic pivot with cte function commands using sp_executesql.

  1. execute sp_executesql @CteWithAgFn;  

For more about dynamic SQL commands.

SQL Server

Summary

In this article, we learned how to create a dynamic pivot with aggregate functions without using tempTable. Also, we learned the usage and purpose of dynamic SQL commands.