编写 SQL 查询以将表从 A 转换为 B [英] Write a SQL query to convert table from A to 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;

这篇关于编写 SQL 查询以将表从 A 转换为 B的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！