Create View using table and column names from data in a table, then build SQL
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