SQL Summary Pivot Table
Posted: Thu Mar 25, 2021 11:35 am
How do I show an SQL table auto-generated like this in a browse_edit form?
Such that when I click one record, it will open one employee record.
Such that when I click one record, it will open one employee record.
Code: Select all
SET session group_concat_max_len=15000;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(certificate.certificate_type_id = ''',
certificate.certificate_type_id,
''', 1, 0)) AS ',
REGEXP_REPLACE(cert_name,'[:space:]|[:punct:]','_',1,0,'c')
)
) INTO @sql
FROM certificate JOIN cert_type ON certificate.certificate_type_id = cert_type.cert_type_id;
SET @sql = CONCAT('SELECT employee.employee_id
, employee.emp_name
, employee.emp_role, ', @sql, '
FROM employee
LEFT JOIN certificate
ON employee.employee_id = certificate.employee_id
GROUP BY employee.employee_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;