Sql-server – How to join unknown table from Xref table

sql server

I have a database that was provided by one of our vendors that I am trying to tie into another system, but I have never come across something designed this way.

I want to search for "sensors" named x, and get their readings as the result. There is one main "Xref" table, and then about 500 other tables that contain the sensor readings.

I can search for my desired sensors with this query, which returns the table name and columns containing the readings:

select * from [Xref] where name like '%Sensor_a%'
----------------------------------------------
Name        TableColumn TableName
----------------------------------------------
Sensor_a1   2429        SensorData12
Sensor_a2   2430        SensorData12

With the results from above, I am able to find the sensor readings from the appropriate table:

select top 1 [Time],[2429],[2430] FROM [SensorData12]
----------------------------------------------
Time                2429        2430
----------------------------------------------
2015-10-01 00:00:48 88.7        37.1

Is there a way to get the readings in one request to the sql-server? I can't figure out how to do it with the dynamic table names that may change depending on the name of the sensor I search for.

Best Answer

One way would be to use a cursor and dynamic sql. Granted the solution below would need to be modified to accommodate your actual data, especially if the number of columns is variable, but for the example you gave it does work.

CREATE TABLE Xref(name varchar(20),TableColumn varchar(10),TableName varchar(20))

CREATE TABLE SensorData12([time] datetime2(0), [2429] decimal(3,1),[2430] decimal(3,1))

INSERT INTO [Xref]
VALUES('Sensor_a1', '2429', 'SensorData12'),
     ('Sensor_a2', '2430', 'SensorData12');

INSERT INTO [SensorData12]
VALUES ('2015-10-01 00:00:48', 88.7 ,37.1)

DECLARE @tablename sysname,
       @col1      sysname,
       @col2      sysname,
       @sql       nvarchar(max);

DECLARE c_tables CURSOR LOCAL
FOR SELECT [Tablename],
         MAX(CASE WHEN [name] = 'Sensor_a1' THEN [TableColumn]
            END) AS [col1],
         MAX(CASE WHEN [name] = 'Sensor_a2' THEN [TableColumn]
            END) AS [col2]
    FROM [Xref]
    GROUP BY [TableName];
OPEN c_tables;

WHILE 1 = 1
    BEGIN
       FETCH NEXT FROM c_tables INTO @tablename,
                               @col1,
                               @col2;

       SET @sql = 'select top 1 [time],[' + @col1 + '],[' + @col2 + '] from [' + @tablename + '];';

       EXEC [sp_executesql]
           @sql;

       IF @@FETCH_STATUS < 0
          BEGIN BREAK
          END;

    END;
CLOSE c_tables;
DEALLOCATE c_tables;

DROP TABLE [Xref];
DROP TABLE [SensorData12];
Related Question