编写 SQL 查询以将表从 A 转换为 B
在 SQL Server 2016 中，我有一个数据库表(表 A)如下所示:
I have a database table (table A) looks like this, in SQL Server 2016:
表A:
ID Group 2018 2019 2020
-----------------------------------------
ID1 Group A 200 300 400
ID2 Group B 100 800 ---
ID2 Group B ---- 500 300
我想编写一个 SQL 查询或类似的东西或报告工具来生成报告/表(将表 A 转换为表 B)，如下所示:
I want to write a SQL query or something like that or a reporting tool to generate a report/table (convert table A to table B) as below:
表 B:
ID Group - Year - Value
----------------------------------------
ID1 Group A 2018 200
ID1 Group A 2019 300
ID1 Group A 2020 400
ID2 Group B 2018 100
ID2 Group B 2019 800
ID2 Group B 2019 500
ID2 Group B 2020 300
如果可以通过编写 SQL 查询来实现，那就太好了.如果需要使用编程语言编写程序，或者使用工具，也可以，但请告诉我使用什么以及如何实现(我知道一些C#编程).
If it can be achieved by writing a SQL query that would be great. If that it needs to use a programming language to write a program, or use a tool, that will also be OK but please let me know what to use and how to achieve (I know some C# programming).
(我知道我不应该使用 ID 和 Group 作为列名.我不会在数据库表中真正使用那个名称，只是为了简化这里的问题)
(I know I should not use ID and Group as the column name. I will not really use that name in the database table, just to simplify the question here)
有人可以帮忙吗?非常感谢！
Anyone can help? Thank you very much!
推荐答案
使用union all的规范方法:
select id, group, 2018 as year, "2018" from t
union all
select id, group, 2019 as year, "2019" from t
union all
select id, group, 2020 as year, "2018" from t;
不过，在SQL Server中，我强烈推荐apply:
However, in SQL Server, I strongly recommend apply:
select t.id, t.group, v.*
from t cross apply
(values (2018, [2018]), (2019, [2019]), (2020, [2020])
) v(year, value)
where v.value is not null;
相关文章