SQL Server - Create view using many fields of a row as names of columns

Question

I present to you a simple and short example of what I want to resolve.

I need to create a view from two tables.

The table indicator stores in a single row the names of the indicators to be measured by the system.

The table sample stores the values of the measurements.

The view I want to make has to look like this:

Is there any way to do this?

Note: The indicators can be more than 100, and can be modified on the way. The solution should be dynamic: SELECT Indicator1 AS 'Temperature' is not an option

I appreciate your comments very much.

Best regards!

Are you saying you want a single view that returns two result sets? — Tab Alleman, Feb 07 '19 at 20:48
I believe this solution requires dynamic sql and permission to execute stored procedures (`EXEC sp_executesql`). Is that available? @tuditec — Neil, Feb 07 '19 at 20:49
You can't do this in a view. The solution you require demands that you use dynamic sql and you can't use dynamic sql in a view. There is no work around there. You would have to use a stored procedure to accomplish this. — Sean Lange, Feb 07 '19 at 20:50
@SeanLange, that's true but how about creating a UDF with dynamic sql and calling it with a View, is it going to work ? — Alvaro Parra, Feb 07 '19 at 21:01
@AlvaroParra UDFs can't call procedures either. By definition a udfs can't have side effects - no modifying server state. They only take inputs and produce outputs. It is possible to create a stored procedure that (re)creates the views OP is looking for that is probably the best solution as long as procedures are available. — Neil, Feb 07 '19 at 21:04
@AlvaroParra no that won't work. You can't have dynamic sql in a function. — Sean Lange, Feb 07 '19 at 21:05
The two views are differents examples, the return should bring only one, of course. And yes, has permissions to exec stored procedures. @Tab Alleman — tuditec, Feb 07 '19 at 21:23
Well, as the comments have established, you cannot have dynamic sql in a view. Are you able to accept a stored procedure instead of a view as a solution? — Tab Alleman, Feb 07 '19 at 21:29
Well, it is clear that it can not be done with a view. I will try with dynamic sql in a sp. — tuditec, Feb 07 '19 at 21:40
https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query --I believe what you're looking to do is possible with dynamic pivot — NonProgrammer, Feb 07 '19 at 21:52

score 1 · Accepted Answer · answered Feb 07 '19 at 21:39

As @Neil and @Sean have both recommended this is only possible via dynamic sql which can be executed within a stored procedure.

As such below is an example of said stored procedure with produces the asked for result (the procedure itself creates and drops the sample tables and data, however you can remove those sections to fit your needs)

create procedure dbo.indicator_per_sample 

    @RegID int 

    as
    begin

    IF OBJECT_ID('tempdb..#indicator') IS NOT NULL DROP TABLE #indicator;
    IF OBJECT_ID('tempdb..#sample') IS NOT NULL DROP TABLE #sample;

create table #indicator (

    RegID int,
    Equipment nvarchar(3),
    Indicator1 nvarchar(50),
    Indicator2 nvarchar(50),
    Indicator3 nvarchar(50)
);

create table #sample (

    SampleID int,
    RegID int,
    Indicator1 int,
    Indicator2 int,
    Indicator3 float
);

insert into #indicator
select * from (values(1, 'AAA', 'Temperature', 'Pressure', 'Oxygen'),
                     (2, 'BBB', 'Power', 'Voltage', 'Current')) x 
(RegID, Equipment, Indicator1, Indicator2, Indicator3);


insert into #sample
select * from (values(1, 1, 25, 1013, 0.87),
                     (2, 2, 261, 12, 4.89),
                     (3, 1, 29, 975, 1.16),
                     (4, 2, 224, 24, 8.78)) y 
(SampleID, RegID, Indicator1, Indicator2, Indicator3);


declare
    @column1 nvarchar(50) ,
    @column2 nvarchar(50) ,
    @column3 nvarchar(50) ,
    @sql nvarchar(max) ,
    @paramdefinition nvarchar(max)

    select @column1 = indicator1, @column2=Indicator2, @column3=Indicator3 from 
#indicator where RegID=@RegID;

    select @sql = 'select s.sampleid, i.Equipment, s.indicator1 as [' + cast(@column1 
as 
nvarchar(50)) + '], 
    s.Indicator2 as [' + cast(@column2 as nvarchar(50)) + '], s.Indicator3 as [' + 
cast(@column3 as nvarchar(50)) + ']
    from #indicator i inner join #sample s on i.RegID=s.RegID and i.RegID=@RegID;';

    select @paramdefinition = '@RegID int'

    exec sp_executesql @sql, @paramdefinition, @RegID=@RegID;

        drop table #indicator, #sample;

end

This can then be called with this syntax

exec indicator_per_sample 1;

Which dynamically names the columns based on the parameter passed in.

SQL Server - Create view using many fields of a row as names of columns

1 Answers1