mysql - MySQL将列转换为行

我试图弄清楚如何计算所有总裁，经理等，然后在一个查询中将列转换为行。
例如，此处有一些示例数据库“员工”：http://www.mysqltutorial.org/tryit/
我可以使用以下查询来统计所有类型的员工：

SELECT
    SUM(CASE
        WHEN jobTitle = 'President' THEN 1
        ELSE 0
    END) AS 'Presidents',
    SUM(CASE
        WHEN jobTitle LIKE 'VP%' THEN 1
        ELSE 0
    END) AS 'VPs',
    SUM(CASE
        WHEN jobTitle LIKE '%Manager%' THEN 1
        ELSE 0
    END) AS 'Managers',
    SUM(CASE
       WHEN jobTitle LIKE '%Rep' THEN 1
       ELSE 0
    END) AS 'Reps'
FROM
employees;

但是现在我想将列转换为行，而且我不知道如何将其包含在类似于此处答案的查询中：
Mysql Convert Column to row (Pivot table )

最佳答案

您可以使用子查询来标准化职位，然后使用case语句对它进行分组和排序，以产生从高到低的输出。

select  jobtitle,count(*) from
(
SELECT  case
        WHEN jobTitle = 'President' THEN 'Presidents'
        WHEN jobTitle LIKE 'VP%' THEN 'VPs'
        WHEN jobTitle LIKE '%Manager%' then 'Managers'
        WHEN jobTitle LIKE '%Rep' then 'Reps'
        end as Jobtitle
FROM
employees
 ) s

group   by Jobtitle
order by
case when jobtitle = 'Presidents' then 1
 when jobtitle = 'VPs' then 2
 when jobtitle = 'Managers' then 3
 when jobtitle = 'Reps' then 4
 end