問題描述

在 SQL Server 2016 中，我有一個數(shù)據(jù)庫表(表 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 轉(zhuǎn)換為表 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 查詢來實現(xiàn)，那就太好了.如果需要使用編程語言編寫程序，或者使用工具，也可以，但請告訴我使用什么以及如何實現(xiàn)(我知道一些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).

(我知道我不應(yīng)該使用 ID 和 Group 作為列名.我不會在數(shù)據(jù)庫表中真正使用那個名稱，只是為了簡化這里的問題)

(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的規(guī)范方法:

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;

這篇關(guān)于編寫 SQL 查詢以將表從 A 轉(zhuǎn)換為 B的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！