时间戳

首页 > 解决方案 > 如何旋转/旋转具有任意列数的多个 SQL 表？

sql - 如何旋转/旋转具有任意列数的多个 SQL 表？

问题描述

我有多个（宽）表：

例子：

宽表1：

| id | timestamp | col1     | col2    | ... | col200    |
|----|-----------|----------|---------|-----|-----------| 
|1   | 11        | 1.001    | 7.004   | ... | 1.240     |
|2   | 12        | 6.003    | NULL    | ... | 2.201     |
|... | ...       | ...      | ...     | ... |  ....     |
|5000| 5010      | 5020.009 | 5001.402| ... | 5085.210  |

宽表2：

| id | timestamp | col201   | col202  | ... | col706 |
|----|-----------|----------|---------|-----|--------| 
|1   | 104       | 7.051    | 1.004   | ... | 6.270  |
|... | ...       | ...      | ...     | ... |  ....  |
|9000| 8010      | NULL     | 301.002 | ... | 535.10 |

我想从这些宽表中创建一个长表，保留前两列，但旋转所有其他列。

我怎样才能将这 2 个表旋转/旋转为一个表以获得这样的结果：

长表：

| id | timestamp | colum_name | value  |
|----|-----------|------------|--------|
|1   |11         |col1        |1.001   |
|1   |11         |col2        |7.004   |
|... |...        |...         |...     |
|1   |11         |col200      |1.240   |
|2   |12         |col1        |6.003   |
|2   |12         |col2        |NULL    |
|... |...        |...         |...     |
|2   |12         |col200      |2.201   |
|... |...        |...         |...     |
|5000|5010       |col1        |5020.009|
|5000|5010       |col2        |5001.402|
|... |...        |...         |...     |
|5000|5010       |col200      |5085.210|
|1   |104        |col201      |7.051   |
|1   |104        |col202      |1.004   |
|... |...        |...         |...     |
|1   |104        |col706      |6.270   |
|... |...        |...         |...     |
|9000|8010       |col201      |NULL    |
|9000|8010       |col202      |301.002 |
|... |...        |...         |...     |
|9000|8010       |col706      |535.10  |

如果可能的话，我不想维护 SQL 语句中的所有列名，因为模式将来会多次更改。

标签： sqlsql-servertsql

解决方案

我看到大量的列。如果是 2016+，您可以使用一点 JSON 来动态反透视您的数据，而无需实际使用动态 SQL。

我应该添加NULL的值将被排除在外。

如果 <2016，则有类似的 XML 方法。

例子

Select A.ID
      ,A.TimeStamp
      ,B.* 
 from  YourTable A
 Cross Apply (
                Select colum_name = [Key]
                      ,[value]
                 From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper ) ) 
                 Where [Key] not in ('ID','timestamp')
             ) B

推荐阅读