Saturday, January 12, 2019

How can we dynamically generate pivot table in MySQL?

Pivot table is an important concept we are using in many applications. Technically, it generate the data values into dynamic columns. One of the examples is attendance information. We store the person name, date and present (boolean that determined if that person is present or absent on this date).

This is how data is stored is database. This is what we want to see in report.
id student date present
1 John 2019-01-07 1
2 David 2019-01-07 1
3 Larry 2019-01-07 1
4 Wesley 2019-01-07 0
5 Amy 2019-01-07 1
6 John 2019-01-08 1
7 David 2019-01-08 0
8 Larry 2019-01-08 1
9 Wesley 2019-01-08 1
10 Amy 2019-01-08 1
11 John 2019-01-09 0
12 David 2019-01-09 1
13 Larry 2019-01-09 1
14 Wesley 2019-01-09 1
15 Amy 2019-01-09 1
16 John 2019-01-10 1
17 David 2019-01-10 1
18 Larry 2019-01-10 0
19 Wesley 2019-01-10 1
20 Amy 2019-01-10 1
21 John 2019-01-11 1
22 David 2019-01-11 1
23 Larry 2019-01-11 0
24 Wesley 2019-01-11 1
25 Amy 2019-01-11 0
date John David Larry Wesley Amy
2019-01-07 1 1 1 0 1
2019-01-08 1 0 1 1 1
2019-01-09 0 1 1 1 1
2019-01-10 1 1 0 1 1
2019-01-11 1 1 0 1 0

SQL Code
SET @dynamic_columns = NULL;
select group_concat(student_col) from (
select DISTINCT CONCAT('MIN(IF(student = ''', student, ''', present, NULL)) AS ', student) as student_col from 
attendance
) column_list INTO @dynamic_columns;
SET @sql = CONCAT('SELECT date, ', @dynamic_columns, ' FROM attendance GROUP BY date');
PREPARE qry FROM @sql;
EXECUTE qry;
DEALLOCATE PREPARE qry;

4 comments:

Image compression using C#

Sometimes, we need to compress the image files while maintaining the image quality. This can be achieved by the following C# implementation....