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;

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

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

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

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