SQL中的透视表，但将度量值名称保留在列中

Question

我无法正确调整表的数据透视。

我的输入是这个原始数据表：

+------+---------+------------+----------+
| YEAR | FACULTY | ADMISSIONS | DROPOUTS |
+------+---------+------------+----------+
| 2018 | LAW     |         15 |        2 |
| 2019 | LAW     |         18 |        4 |
| 2020 | LAW     |         11 |        1 |
| 2018 | MATH    |         19 |        1 |
| 2019 | MATH    |         17 |        6 |
| 2020 | MATH    |         24 |        5 |
+------+---------+------------+----------+

我想把年份改为连续几年，但我也想把录取和辍学的衡量标准作为连续几年的名字。E.g我想要一张这样的桌子：

+---------+------------+------+------+------+
| FACULTY |  MEASURE   | 2018 | 2019 | 2020 |
+---------+------------+------+------+------+
| LAW     | ADMISSIONS |   15 |   18 |   11 |
| LAW     | DROPOUTS   |    2 |    4 |    1 |
| MATH    | ADMISSIONS |   19 |   17 |   24 |
| MATH    | DROPOUTS   |    1 |    6 |    5 |
+---------+------------+------+------+------+

我可以使用调整年份

SELECT * 
FROM 
(
SELECT FACULTY, YEAR, ADMINISSION, DROPPUTS 
FROM TABLE
PIVOT (SUM (ADMISSIONS) 
FOR YEAR IN (2018,2019,2020)
)

但我需要调整两个度量值，并且仍然获得度量值名称列。有什么想法吗？

Answer 1

这是先取消平移，然后再旋转。如果您的数据库支持横向联接和values()，则可以执行以下操作：

select
t.faculty,
x.measure,
sum(case when t.year = 2018 then x.value end) value_2018,
sum(case when t.year = 2019 then x.value end) value_2019,
sum(case when t.year = 2020 then x.value end) value_2020
from mytable t
cross apply (values ('admission', admission), ('dropout', dropout)) as x(measure, value)
group by t.faculty, x.measure

Answer 2

我会使用apply取消预览(假设您使用的是SQL Server(并重新聚合：

select t.faculty, v.measure,
max(case when year = 2018 then val end) as [2018],
max(case when year = 2019 then val end) as [2019],
max(case when year = 2020 then val end) as [2020]
from t cross apply
(values ('ADMISSIONS', ADMISSIONS), ('DROPOUTS', DROPOUTS)
) v(measure, val)
group by t.faculty, v.measure