mysql - 我正在寻找一种有效的方法将 mysql 中的多个空列合并到单个表中

Question

Result set

我需要一种有效的方法将每一行合并为一列。

这是我的sql语句。

 SELECT students.MatricNo,
(CASE WHEN courses.Code = 'KHE 101' THEN results.Total ELSE '' END) AS 'KHE101',
(CASE WHEN courses.Code = 'KHE 102' THEN results.Total ELSE '' END) AS 'KHE102',
(CASE WHEN courses.Code = 'KHE 103' THEN results.Total ELSE '' END) AS 'KHE103',
(CASE WHEN courses.Code = 'KHE 104' THEN results.Total ELSE '' END) AS 'KHE104',
(CASE WHEN courses.Code = 'TEE 103' THEN results.Total ELSE '' END) AS 'TEE103',
(CASE WHEN courses.Code = 'TEE 128' THEN results.Total ELSE '' END) AS 'TEE128',
(CASE WHEN courses.Code = 'GCE 101' THEN results.Total ELSE '' END) AS 'GCE101',
(CASE WHEN courses.Code = 'KHE 105' THEN results.Total ELSE '' END) AS 'KHE105',
(CASE WHEN courses.Code = 'KHE 107' THEN results.Total ELSE '' END) AS 'KHE107',
(CASE WHEN courses.Code = 'KHE 108' THEN results.Total ELSE '' END) AS 'KHE108',
(CASE WHEN courses.Code = 'KHE 109' THEN results.Total ELSE '' END) AS 'KHE109', 
(CASE WHEN courses.Code = 'TEE 102' THEN results.Total ELSE '' END) AS 'TEE102',
(CASE WHEN courses.Code = 'GES 107' THEN results.Total ELSE '' END) AS 'GES107',
(CASE WHEN courses.Code = 'SPE 104' THEN results.Total ELSE '' END) AS 'SPE104'


FROM results
INNER JOIN students ON results.Student = students.Guid
INNER JOIN courses ON results.Course = courses.Guid
INNER JOIN departments ON students.Department = departments.Guid

WHERE departments.Code = 'KHE' AND results.Level = 100

Answer 1

您需要调整数据。
这是通过使用 GROUP BY 和 MAX 函数来完成的。

查询

    SELECT 
        unpivotted.MatricNo
      , MAX(unpivotted.KHE101) AS KHE101
      ...
      ...
      , MAX(unpivotted.SPE104) AS SPE104
    FROM (

     SELECT students.MatricNo,
    (CASE WHEN courses.Code = 'KHE 101' THEN results.Total ELSE '' END) AS 'KHE101',
    (CASE WHEN courses.Code = 'KHE 102' THEN results.Total ELSE '' END) AS 'KHE102',
    (CASE WHEN courses.Code = 'KHE 103' THEN results.Total ELSE '' END) AS 'KHE103',
    (CASE WHEN courses.Code = 'KHE 104' THEN results.Total ELSE '' END) AS 'KHE104',
    (CASE WHEN courses.Code = 'TEE 103' THEN results.Total ELSE '' END) AS 'TEE103',
    (CASE WHEN courses.Code = 'TEE 128' THEN results.Total ELSE '' END) AS 'TEE128',
    (CASE WHEN courses.Code = 'GCE 101' THEN results.Total ELSE '' END) AS 'GCE101',
    (CASE WHEN courses.Code = 'KHE 105' THEN results.Total ELSE '' END) AS 'KHE105',
    (CASE WHEN courses.Code = 'KHE 107' THEN results.Total ELSE '' END) AS 'KHE107',
    (CASE WHEN courses.Code = 'KHE 108' THEN results.Total ELSE '' END) AS 'KHE108',
    (CASE WHEN courses.Code = 'KHE 109' THEN results.Total ELSE '' END) AS 'KHE109', 
    (CASE WHEN courses.Code = 'TEE 102' THEN results.Total ELSE '' END) AS 'TEE102',
    (CASE WHEN courses.Code = 'GES 107' THEN results.Total ELSE '' END) AS 'GES107',
    (CASE WHEN courses.Code = 'SPE 104' THEN results.Total ELSE '' END) AS 'SPE104'


    FROM results
    INNER JOIN students ON results.Student = students.Guid
    INNER JOIN courses ON results.Course = courses.Guid
    INNER JOIN departments ON students.Department = departments.Guid

    WHERE departments.Code = 'KHE' AND results.Level = 100

    ) AS unpivotted

GROUP BY 
  unpivotted.MatricNo
ORDER BY
  unpivotted.MatricNo ASC