时间戳

首页 > 解决方案 > 编写 SQL 查询将表从 A 转换为 B

sql - 编写 SQL 查询将表从 A 转换为 B

问题描述

我在 SQL Server 2016 中有一个数据库表（表 A），如下所示：

表 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），如下所示：

表 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# 编程）。

（我知道我不应该使用 ID 和 Group 作为列名。我不会在数据库表中真正使用该名称，只是为了简化这里的问题）

任何人都可以帮忙吗？非常感谢！

标签： sqlsql-serverunpivot

解决方案

以防万一您随着时间的推移有可变列或附加列。

请注意，您只需要 EXCLUDE 某些列并省略 NULLS。

全面披露：Gordon 的 APPLY 性能更佳。

例子

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

推荐阅读