mysql - 如何将多行合并为表中的单行

Question

我有一个类似的数据库

我需要把这张表转换成这种格式

标签： mysqlsqloracle

Answer 1

您可以将查询与PIVOT操作一起使用

询问

WITH
    marks_data (id, subject, marks)
    AS
        (SELECT 1, 'maths', 100 FROM DUAL
         UNION ALL
         SELECT 1, 'physics', 95 FROM DUAL
         UNION ALL
         SELECT 1, 'chemistry', 99 FROM DUAL
         UNION ALL
         SELECT 2, 'maths', 90 FROM DUAL
         UNION ALL
         SELECT 2, 'physics', 89 FROM DUAL
         UNION ALL
         SELECT 2, 'chemistry', 96 FROM DUAL)
SELECT *
  FROM (SELECT * FROM marks_data)
       PIVOT (MAX (subject) AS subject, MAX (marks) AS marks
             FOR subject
             IN ('maths' AS maths, 'physics' AS physics, 'chemistry' AS chemistry));

结果

   ID    MATHS_SUBJECT    MATHS_MARKS    PHYSICS_SUBJECT    PHYSICS_MARKS    CHEMISTRY_SUBJECT    CHEMISTRY_MARKS
_____ ________________ ______________ __________________ ________________ ____________________ __________________
    1 maths                       100 physics                          95 chemistry                            99
    2 maths                        90 physics                          89 chemistry                            96