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

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!

Answer

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.