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

Question

我有多张(宽的)桌子，上面有：

• 大量列
• 列名在表
• 之间不匹配，ID-s在表

之间重叠

示例：

wide_table1

| 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  |

wide_table2

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

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

我怎样才能把这两个表旋转成一个，得到这样的结果：

long_table

| 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语句中的所有列名，因为模式在将来会多次更改。

Answer 1

使用union all和apply

select t1.id, t1.timestamp, v.*
from t1 cross apply
     (values ('col1', t1.col1),
             ('col2', t1.col2),
             . . .
     ) v(colname, value)
union all
select t2.id, t2.timestamp, v.*
from t2 cross apply
     (values ('col1', t1.col1),
             ('col2', t1.col2),
             . . .
     ) v(colname, value);

Answer 2

我看到了很多专栏。如果是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

Answer 3

您可能希望为此使用动态SQL：

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @Delimiter nvarchar(100) = ',' + @CRLF + N'                        ';

SET @SQL = N'SELECT YT.Id,' + @CRLF + 
           N'       YT.[timestamp],' + @CRLF +  
           N'       V.[ColumnName],' + @CRLF + 
           N'       V.ColumnValue' + @CRLF   +
           N'FROM dbo.YourTable YT' + 
           N'     CROSS APPLY (VALUES' +
           (SELECT STRING_AGG('(N' + QUOTENAME(c.[name],'''') + N',' + QUOTENAME(c.[name]) + N')',@Delimiter) WITHIN GROUP (ORDER BY C.column_id)
            FROM sys.schemas s
                 JOIN sys.tables t ON s.schema_id = t.schema_id
                 JOIN sys.columns c ON t.object_id = c.object_id
            WHERE s.[name] = N'dbo'
              AND t.[name] = N'YourTable') + N')V(ColumnName,ColumnValue);';

PRINT @SQL;-- Use SELECT for values over 4,000 characters

EXEC sp_executesql @SQL;

显然，用正确的表和模式名称替换YourTable和dbo。