0

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

1

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

  • Wonderful, I struggled with this. @tabname actually comes from 'mytable', there are many tables (each site has a table). The view will hold thousands of records that will include all of the tablenames in the table. I could either exec the sp_generate_view for each table, but that might be performance prohibitive. Or, I could try getting all of the data in the sp and then exec without passing a parameter. Do you have any thoughts on the best approach? – Sherrie Feb 22 '18 at 20:47
  • I don't know exactly what are you trying to achieve and what is your current table schema, but if you should execute it recursively for a lot of tables you should consider to do all the job within the stored procedure. – McNets Feb 23 '18 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.