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.
