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
  3. IF OBJECT_ID (N'dbo.student_Register') IS NOT NULL
  4. drop table [student_Register]
  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
  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)
  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,'')
  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.

  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
  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])
  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. (
  5. '+@query+'
  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. )
  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.