問題描述
在 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中，我強(qiáng)烈推薦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)！