转置MySQL查询的结果 [英] Transpose the results of a MySQL query

问题描述

我想将MySQL查询的结果从每行键=>值结果集转置到结果集，其中键是列标题，而值是该列的行条目.

I'd like to transpose the results of a MySQL query from a per row key => value resultset to a resultset where the key is the column header and the value is a row entry for that column.

即给出以下数据

|------------------+-------------|
|  CLASS_LESSON    | ATTENDANTS  |
|------------------+-------------|
|  class1art       | 1           |
|  class1history   | 1           |
|  class2geography | 2           |
|------------------+-------------|

我想将其转换为

|------------+---------------+------------------|
|  class1art | class1history | class2geography  |
|------------+---------------+------------------|
|  1         | 1             | 2                |
|------------+---------------+------------------|

假设班级/课程对是动态的；可以随时添加或删除它们.我不想按照典型的数据透视表"中的建议明确地将它们叫出sql 解决方案.

Assume that the class/lesson pairs are dynamic; they can be added or removed at any time. I don't want to explicitly call them out as suggested in the typical 'pivot table' sql solution.

select 
  MAX(CASE WHEN class_lesson = 'class1art' THEN attendants ELSE 0 END) AS class1art,
  MAX(CASE WHEN class_lesson = 'class1history' THEN attendants ELSE 0 END) AS class1history,
  MAX(CASE WHEN class_lesson = 'class2geography' THEN attendants ELSE 0 END) AS class2geography,
  MAX(CASE WHEN class_lesson = 'class7art' THEN attendants ELSE 0 END) AS class7art,
  MAX(CASE WHEN class_lesson = 'class7history' THEN attendants ELSE 0 END) AS class7history

from
    (select 
        group_concat(distinct class, lesson) as class_lesson,
            count(*) as attendants
    from
        TableName
    group by class , lesson
    ) a

这是一个带有示例数据的 SQLFiddle 环境. 不使用存储过程就可以吗?

Here's an SQLFiddle environment with sample data. Is this possible without utilizing stored procedures?

推荐答案

尝试一下

SELECT  
MAX(CASE WHEN t.CLASS_LESSON = 'class1art' THEN t.ATTENDANTS ELSE NULL END) AS class1art,
MAX(CASE WHEN t.CLASS_LESSON = 'class1history' THEN t.ATTENDANTS ELSE NULL END) AS class1history,
MAX(CASE WHEN t.CLASS_LESSON = 'class2geography' THEN t.ATTENDANTS ELSE NULL END) AS class2geography
FROM 
(
 select 
 group_concat(distinct class, lesson) as class_lesson, count(*) as attendants

 from 
 TableName

 group by 
 class, lesson
) as t

这篇关于转置MySQL查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！