Post

# Aggregate Functions In Dynamic Pivot Using Common Table Expression

• 15.5k
• 0
• 2

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

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 +','

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,'')

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] ‘

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'

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.

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.

Recommended Free Ebook
Similar Articles