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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
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;
Great experience for me by reading this blog. Nice article.
ReplyDeleteStruts Training in Chennai
Struts Training institutes in Chennai
struts Training in T Nagar
struts Training in OMR
Wordpress Training in Chennai
Wordpress Training
Spring Training in Chennai
Hibernate Training in Chennai
Thank you, Velraj.
ReplyDeleteDynamically generating pivots involves dynamic column selection and aggregation. Utilize programming languages like Python or tools such as Pandas or SQL's dynamicIs Wifi Free pivot queries.
ReplyDeletehttps://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
TAD62D