SQL Summary Pivot Table

[ernesttan1976]

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.

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;

[ernesttan1976]

The 4th column onwards are generated on the fly and not known in advance.

[kev1n]

The Browse Form supports a predefined number of columns along with their column names. I currently see no easy way to display a dynamic dataset.

Maybe you can resort to another grid like https://datatables.net/

[ernesttan1976]

Thanks Kevin