Server:连接表中的所有表，列出它们的名称

Question

我有一个表，其中包含一个格式的度量及其in列表：

L6Metrics
| ID   | Name            |
--------------------------
| 734  | L6A_Velocity    |
| 736  | L6B_Velocity    |
| 738  | L6A_Target_Rate |
...

每个指标都有自己的相应表，其中包含有时间戳的历史数据。如果度量有ID X，那么它的历史数据的表名是History_tX。这些历史表的格式如下：

History_t734
| DateTime                       | Value |
-----------------------------------------
| 2018-05-04 19:17:41.972 -05:00 | 33.9  |
| 2018-05-04 19:17:51.972 -05:00 | 36.3  |
| 2018-05-04 19:18:01.972 -05:00 | 35.1  |
...

假设跨越所有历史表的时间戳对齐，如何使用L6Metrics表连接DateTime上的所有历史表以获得以下信息：

| DateTime                       | History_t734 | History_t736 | ...
---------------------------------------------------------------- 
| 2018-05-04 19:17:41.972 -05:00 | 33.9         | 34.9         |
| 2018-05-04 19:17:51.972 -05:00 | 36.3         | 37.4         |
| 2018-05-04 19:18:01.972 -05:00 | 35.1         | 36.5         |
...

L6Metrics中有75行(代表75个历史表)，因此我的结果中将有76列。如果需要，我有能力修改L6Metrics。

Answer 1

虽然我非常同意伦纳特的评论的观点：

然后我建议将它们合并为1，并为类型添加一个属性。

...I明白，我们并不总是有一个魔杖来修复别人的(糟糕的)设计。因此，我选择一种动态SQL方法：

DECLARE @sqlcol nvarchar(max) = N'SELECT t0.DateTime', @sqljoin nvarchar(max) = N'';

;WITH tnames AS (SELECT t = N'History_t' + CONVERT(varchar(11),ID),
  rn = ROW_NUMBER() OVER (ORDER BY ID) FROM dbo.L6Metrics)
SELECT @sqlcol += N',
' + t + N' = ' + t + N'.Value', 
  @sqljoin += CASE WHEN rn = 1 THEN N'
  FROM dbo.' + t + N' AS t0' ELSE N'
  LEFT OUTER JOIN dbo.' + t
  + N' ON t0.DateTime = ' + t + N'.DateTime' END
FROM tnames;

PRINT @sqlcol;
PRINT @sqljoin; 

-- when the PRINT output looks right, uncomment these:

-- SET @sqlcol += @sqljoin;
-- EXEC sys.sp_executesql @sqlcol;

给定样本数据，PRINT输出的结果如下：

SELECT t0.DateTime,
History_t734 = History_t734.Value,
History_t736 = History_t736.Value,
History_t738 = History_t738.Value

  FROM dbo.History_t734 AS t0
  LEFT OUTER JOIN dbo.History_t736 ON t0.DateTime = History_t736.DateTime
  LEFT OUTER JOIN dbo.History_t738 ON t0.DateTime = History_t738.DateTime

当然，这一解决方案在很大程度上依赖于这一假设：

假设跨越所有历史表的时间戳对齐，

因此，提供所有时间戳的“锚”表的选择是任意的。

Answer 2

假设Metrics.ID列表是静态的：

WITH cte AS ( SELECT DateTime FROM History_t1
              UNION
              SELECT DateTime FROM History_t2
              UNION
              ...
              UNION
              SELECT DateTime FROM History_t75
            )
SELECT cte.Datetime, 
       History_t1.value History_t1_value,
       History_t2.value History_t2_value,
       ...
       History_t75.value History_t75_value
FROM cte
LEFT JOIN History_t1 ON cte.Datetime = History_t1.datetime
LEFT JOIN History_t2 ON cte.Datetime = History_t2.datetime
...
LEFT JOIN History_t75 ON cte.Datetime = History_t75.datetime

如果如您所声称的那样，“假设所有历史表的时间戳对齐”，您可以避免使用CTE：

SELECT History_t1.Datetime, 
       History_t1.value History_t1_value,
       History_t2.value History_t2_value,
       ...
       History_t75.value History_t75_value
FROM History_t1
LEFT JOIN History_t2 ON History_t1.Datetime = History_t2.datetime
...
LEFT JOIN History_t75 ON History_t1.Datetime = History_t75.datetime

根本不需要计量表。

如果Metrics.ID列表是动态的，则使用dynamic构建类似于上面的查询文本。