SQL Server: Join all tables from a table listing their names [duplicate]

Question

I have a table that contains a list of metrics and their IDs in the format:

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

Each of these metrics has its own corresponding table containing timestamped historical data. If the metric has ID X, then its historical data's table name is History_tX. These history tables have the format:

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

Assuming that the timestamps across all of the history tables align, how can I use the L6Metrics table to join all of the history tables on DateTime to get the following:

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

There are 75 rows in L6Metrics (representing 75 history tables) so there would be 76 columns in my result. I have the ability to modify L6Metrics if needed.

Answer 1

While I strongly agree with Lennart's comment:

Then I suggest to merge them into 1 and add an attribute for type

...I understand that we don't always have a magic wand to fix someone else's (bad) design. So I would opt for a dynamic SQL approach:

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;

Given the sample data, results of the PRINT output are:

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

This solution of course relies heavily on this assumption:

Assuming that the timestamps across all of the history tables align,

And so the choice of the "anchor" table, which provides all of the timestamps, is simply arbitrary.

Answer 2

Assuming the list of Metrics.ID is static:

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

If, as you claim, "Assuming that the timestamps across all of the history tables align" you can avoid of 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

The Metrics table not needed at all.

If the list of Metrics.ID is dynamic, use dynamic SQL to build the query text similar to above.