规范方法使用 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 :

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;

以防随着时间的推移出现变量或附加列。
请注意，您只需排除某些列，并省略空值。
充分披露：戈登的申请是一个更有效的推动。
例子

Select A.[ID]
      ,A.[Group]
      ,[Year] = B.[Key]
      ,[Value] = try_convert(int,B.[Value])  --<< choose the proper data type (optional)
 From  YourTable A
 Cross Apply (
                Select *
                 From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) ) 
                 Where [Key] not in ('ID','Group')
             ) 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

编辑—如果不是2016，则为xml版本+

Select A.[ID]
      ,A.[Group]
      ,[Year]  = replace(replace(C.[Item],'X003',''),'_','')
      ,[Value] = try_convert(int,C.[Value])  --<< choose the proper data type (optional)
 From  YourTable A
 Cross Apply ( values ( convert(xml,(Select A.* for XML RAW)) ) ) B(XMLData)
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)', 'varchar(100)') not in ('ID','Group')
             ) C

使用unpivot的解决方案：

select * from A
UNPIVOT 
(
   [value] for [Year] in ([2018],[2019],[2020])
)u;

希望有帮助。！

这是我的解决方案，完全符合你的需要
我将关键字和年份用方括号括起来作为列名

Select id, [Group], [year], value
from
(
select id, [Group], 2018 as [year], [2018] as value from a
union all
select id, [Group], 2019 as [year], [2019] as value from a
union all
select id, [Group], 2020 as [year], [2020] as value from a
) Q
Where value is not null
order by id, [Group], [year]

这是小提琴