如何使用SQL将二维表转换为三维表
如何使用SQL将二维表转换为三维表
提问于 2014-09-11 14:34:09
如何使用SQL将二维表转换为三维表
样本表：
实际表
School class number
S1 I 23
S1 II 12
S1 III 54
S2 I 57
S2 II 12
S2 III 81
S3 I 12
S3 II 25
S3 III 65并将其转换为表
I II III
S1 23 12 54
S2 57 12 81
S3 12 25 65回答 1
Stack Overflow用户
回答已采纳
发布于 2014-09-11 15:25:40
正如肖恩·兰格( Sean )所说，如果你的体重是11克或更高，那么就使用枢轴条款吧：
select *
from classes
pivot (max(class_size) as class_size
for (class) in ('I' as i, 'II' as ii, 'III' as iii))
order by school;
SCHOOL I_CLASS_SIZE II_CLASS_SIZE III_CLASS_SIZE
------ ------------ ------------- --------------
S1 23 12 54
S2 57 12 81
S3 12 25 65 SQL Fiddle
如果您仍然处于不支持支点的早期版本，那么您可以使用手动方法来做同样的事情：
select school,
max(case when class = 'I' then class_size end) as i,
max(case when class = 'II' then class_size end) as ii,
max(case when class = 'III' then class_size end) as iii
from classes
group by school
order by school;
SCHOOL I II III
------ ---------- ---------- ----------
S1 23 12 54
S2 57 12 81
S3 12 25 65 SQL Fiddle。
要显示每个学校的总数，只需添加一个sum
select school,
max(case when class = 'I' then class_size end) as i,
max(case when class = 'II' then class_size end) as ii,
max(case when class = 'III' then class_size end) as iii,
sum(class_size) as total
from classes
group by school
order by school;SQL Fiddle。
要将列之和，可以使用rollup()
select school,
max(case when class = 'I' then class_size end) as i,
max(case when class = 'II' then class_size end) as ii,
max(case when class = 'III' then class_size end) as iii,
sum(class_size) as total
from classes
group by rollup(school)
order by school;
SCHOOL I II III TOTAL
------ ---------- ---------- ---------- ----------
S1 23 12 54 89
S2 57 12 81 150
S3 12 25 65 102
57 25 81 341 SQL Fiddle。但这可能是你应该在客户/应用程序中做的事情。例如，SQL*Plus可以使用它的compute命令自动完成这一任务。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25790263
复制相关文章
相似问题
腾讯云开发者
