SQL Server – Creating Views Using Table and Column Names

sql serversql-server-2016

I need to create a view using data in a table for the table name and column name. Preferably I’d like to do it with set processing. I have a sample of doing this for one record in the table with a script of stored procedure, which won’t do for the application. The resulting view pulls data from a data logger for display on a dashboard, so it needs to be current and fast. That’s why I think a view would work best, but I’m having trouble coming up with the SQL.

I have a table that looks like the following. This is an example of two records, there are tens of thousands of them. There will be one or more duplicate table names, which corresponds with one or more column names in the LOGGER_FIELD column.
Input Table

From data in this table, I’d like to create a view that would have this sql:

select TmStamp, site_id, value 
from
     (select TmStamp, G0025890, G0025891 
      from   [BIG SLOUGH (STN 953)_HOURLY] 
      where TmStamp = (select max(TmStamp) from [BIG SLOUGH (STN 953)_HOURLY])) p
unpivot
(value FOR site_id IN (G0025890, G0025891))AS unpvt;

The table [BIG SLOUGH (STN 953)_HOURLY] looks like:
Big Slough Table

The table name in the FROM is tabname in the view, and the values in LOGGER_FIELD are the column names. The ending view will look like:

Final View

Best Answer

You can use a dynamic query.

create table my_table (tabname varchar(200), site_id int, logger_field varchar(200));

insert into my_table values
('[big_slough]', 2589, 'G0025890'),
('[big_slough]', 2590, 'G0025891');


create table [big_slough] 
(
    TmStamp datetime, 
    RecNum int, 
    Batt_Volt decimal(18,2), 
    G0025890 decimal(18,2), 
    G0025891 decimal(18,2)
);

insert into [big_slough] values 
('20170725 08:00:00', 0, 13.25, 32.52, 29.63229),
('20170725 09:00:00', 1, 13.48, 32.51, 29.61947),
('20170725 10:00:00', 2, 14.07, 32.50, 29.61757),
('20170725 11:00:00', 3, 14.14, 32.49, 29.61356),
('20170725 12:00:00', 4, 13.43, 32.49, 29.61185),
('20170725 13:00:00', 5, 13.34, 32.50, 29.60452);

GO

8 rows affected

create procedure sp_generate_view
(
@tabname varchar(200)
)
as
begin

    declare @cols nvarchar(max);
    declare @cmd nvarchar(max);

    set @cols = stuff((select ',' + quotename(logger_field) 
                       from my_table
                       for xml path(''), 
                       type).value('.', 'nvarchar(max)'),1,1,'');

     set @cmd = 'select TmStamp, site_id, value 
                 from
                      (select TmStamp, ' + @cols + '
                       from   ' + @tabname + '
                       where TmStamp = (select max(TmStamp) 
                                        from ' + @tabname + ')) p
                 unpivot
                 (value FOR site_id IN (' + @cols + ')) AS unpvt';

    exec (@cmd);

end
GO

✓

exec sp_generate_view @tabname = '[big_slough]';
GO

TmStamp             | site_id  | value
:------------------ | :------- | :----
25/07/2017 13:00:00 | G0025890 | 32.50
25/07/2017 13:00:00 | G0025891 | 29.60

dbfiddle here

Related Question