sql - SQL change a table into another format
问题描述
I have a table like this:
Faculty Program Number_of_enrolled
Science Financial Modelling 25
Science Actuarial Science 30
Science Statistics 28
Science Biology 16
I want to use SQL to change the table into something like this:
Faculty Financial Modelling Actuarial Science Statistics Biology
Science 25 30 28 16
What should I do?
解决方案
Use conditional aggregation:
select
faculty,
max(case when program = 'Financial Modelling' then number_of_enrolled end) [Financial Modelling],
max(case when program = 'Actuarial Science' then number_of_enrolled end) [Actuarial Science],
max(case when program = 'Statistics' then number_of_enrolled end) Statistics,
max(case when program = 'Biology' then number_of_enrolled end) Biology
from mytable
group by faculty
推荐阅读
- sql-server - SQL Server 错误:“'xyz' 附近的语法不正确”
- java - Kotlin:为 guice 注入的依赖项添加一个接口,以便我们可以模拟它们仅用于单元测试目的?
- java - 控制批量 api ElasticSearch 的超时
- python - 通过运行函数设置变量
- java - 未经许可的 iTextPdf setEncryption
- python - Python没有结果和KeyError:1
- c++ - 连接到远程存储库并获取数据
- postgresql - Postgres 中是否有 varchar 字段的 diff/patch 函数/库
- javascript - 复制要在 JavaScript 中迭代的 div 的最佳方法是什么?
- javascript - 使用 javascript 代理拦截方法调用时非法调用