将多行转换为一行，如果不是所有行都有所有列，则转换为多列

Question

我继承了一些格式糟糕的数据，最初是一个电子表格，后来变成了SQL表，我使用SQL(LibreOfficeBase作为原型，然后可能是MySQL(来查询数据。

我的源数据看起来像：

>>opt4优秀历史杰出信息技术数学优秀法语杰出数学好优秀

opt1	teach_opt1	opt2	teach _opt2	opt3	teach _opt 3
法语		需要改进
历史	好	德语

您可以使用条件聚合来调整中间结果，如下所示。

包装您当前的查询(无订购条件(：

select subject,
max(case when judgement='Outstanding' then total else 0 end) as Outstanding,
max(case when judgement='Good' then total else 0 end) as Good,
max(case when judgement='Requires improvement' then total else 0 end) as RequiresImprovement
from (
select subject, judgement, Sum(teaching) as total from (
(select opt1 as subject, teach_opt1 as judgement, Count(*) as teaching from raw where opt1 not like 'None' and opt1 not like '' and teach_opt1 not like 'Don%do it' group by opt1, teach_opt1)
union all
(select opt2 as subject, teach_opt2 as judgement, Count(*) as teaching from raw where opt2 not like 'None' and opt2 not like '' and teach_opt2 not like 'Don%do it' group by opt2, teach_opt2)
union all
(select opt3 as subject, teach_opt3 as judgement, Count(*) as teaching from raw where opt3 not like 'None' and opt3 not like '' and teach_opt3 not like 'Don%do it' group by opt3, teach_opt3)
union all
(select opt4 as subject, teach_opt4 as judgement, Count(*) as teaching from raw where opt4 not like 'None' and opt4 not like '' and teach_opt4 not like 'Don%do it' group by opt4, teach_opt4)
) t
group by subject, judgement
)s
group by subject
order by subject