操作结果以将行显示为列 [英] Manipulate results to display rows as columns

问题描述

我有两张桌子.表A包含部门列表，共20个.

看图

表 B 至少包含表 A 中每个部门的 1 行，有些包含一些.

看图

我想要的是从 A & 创建的第三张表B 基本上列出了每个部门，然后是全职和兼职的人数.例如，如果在表 b 中找到一个部门，它只有全职或兼职的数字(例如部门 D)，那么我希望该表显示 0(零)而不是留空.

看图

有人可以就此提出建议吗?

编辑例如，如果其中一个部门没有兼职"，这意味着他们的兼职员工数量通常为零.

解决方案

你想要一个 枢轴

选择 *来自 (tableb) s枢轴 (Max(staffno) for employee_class in ([Full Time],[Part Time])) p

I have two tables. Table A contains a list of departments, there are 20 in total.

See image

Table B contains at least 1 row per department found in Table A, with some containing a few.

See image

What I want is a 3rd table created from A & B which basically lists every department and then the number of people who are full time and part time. If for example there is a department found in table b which only has a figure for either Full or Part time (an example of this is department D) then I want the table to display 0 (zero) instead of leaving this blank.

See image

Can anyone advise on this?

EDIT If there is no for example 'Part time' for one of the Departments, that means that their part time staff count WILL be zero as a rule.

解决方案

You want a PIVOT

select *
from (tableb) s
pivot (Max(staffno) for employee_class in ([Full Time],[Part Time])) p

这篇关于操作结果以将行显示为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！