Using Subqueries to Define Column Alias

Question

I have two tables which I have simplified below for clarity. One stores data values while the other defines the units and type of data. Some tests have one result, others may have more (My actual table has results 1-10):

Table 'Tests':

ID            Result1        Result2        TestType(FK to TestTypes Type)
----------    ------------   -----------    -----------
1001           50            29             1
1002           90.9          NULL           2
1003           12.4          NULL           2
1004           20.2          30             1

Table 'TestTypes':

Type        TestName         Result1Name    Result1Unit   Result2Name     Result2Unit   ..........
-------     ---------        ------------   -----------   ------------    -----------
1           Temp Calib.      Temperature    F             Variance        %
2           Clarity          Turbidity      CU            NULL            NULL

I would like to use the ResultXName as the column alias when I join the two tables. In other words, if a user wants to see all Type 1 'Temp Calib' tests, the data would be formatted as follows:

Temperature      Variance         
------------     -----------
50 F             10.1%
20.2 F           4.4%

Or if they look at Type 2, which only uses 1 result and should ignore the NULL:

Turbidity
----------
90.9 CU
12.4 CU

I have had some success in combining the two columns of the tables:

SELECT CONCAT(Result1, ' ', ISNULL(Result1Unit, ''))
FROM Tests 
INNER JOIN TestTypes ON Tests.TestType = TestTypes.Type

But I cannot figure out how to use the TestName as the new column alias. This is what I've been trying using a subquery, but it seems subqueries are not allowed in the AS clause:

SELECT CONCAT(Result1, ' ', ISNULL(Result1Unit, '')) AS (SELECT TOP(1) Result1Name FROM TestTypes WHERE Type = 1)
FROM Tests 
INNER JOIN TestTypes ON Tests.TestType = TestTypes.Type

Is there a different method I can use? Or do I need to restructure my data to achieve this? I am using MSSQL.

Answer 1

Yes, this can be fully automated by constructing a dynamic SQL string carefully. The key points in this solution and references is listed as follows.

1. Count the Result variables (section 1.)
2. Get the new column name of ResultXName by using sp_executesql with the output definition (section 2-1)
3. Append the clause for the new column (section 2-2)

N.B.1. Although a dynamic table schema is usually considered a bad design, sometimes people are simply ordered to do that. Therefore I do not question the adequacy of this requirement.

N.B.2. Mind the security problem of arbitrary string execution. Additional string filters may be required depending on your use case.

Test Dataset

use [testdb];
GO

if OBJECT_ID('testdb..Tests') is not null
    drop table testdb..Tests;
create table [Tests] (
    [ID] int,
    Result1 float,
    Result2 float,
    TestType int
)
insert into [Tests]([ID], Result1, Result2, TestType)
values (1001,50,29,1),
       (1002,90.9,NULL,2),
       (1003,12.4,NULL,2),
       (1004,20.2,30,1);

if OBJECT_ID('testdb..TestTypes') is not null
    drop table testdb..TestTypes;
create table [TestTypes] (
    [Type] int,
    TestName varchar(50),
    Result1Name varchar(50),
    Result1Unit varchar(50),
    Result2Name varchar(50),
    Result2Unit varchar(50)
)
insert into [TestTypes]([Type], TestName, Result1Name, Result1Unit, Result2Name, Result2Unit)
values (1,'Temp Calib.','Temperature','F','Variance','%'),
       (2,'Clarity','Turbidity','CU',NULL,NULL);

--select * from [Tests];
--select * from [TestTypes];

Solution

/* Input Parameter */
declare @type_no int = 1;

/* 1. determine the number of Results */

declare @n int;

-- If there are hundreds of results please use the method as of (2-1)
select @n = LEN(COALESCE(LEFT(Result1Name,1),'')) 
          + LEN(COALESCE(LEFT(Result2Name,1),''))
FROM [TestTypes]
where [Type] = @type_no;

/* 2. build dynamic query string */

-- cast type number as string
declare @s_type varchar(10) = cast(@type_no as varchar(10));
-- sql query string
declare @sql nvarchar(max) = '';
declare @sql_colname nvarchar(max) = '';    
-- loop variables
declare @i int = 1;  -- loop index
declare @s varchar(10);  -- stringified @i
declare @colname varchar(max);  -- new column name

set @sql += '
select
    L.[ID]';

-- add columns one by one
while @i <= @n begin

    set @s = cast(@i as varchar(10));

    -- (2-1) find the new column name
    SET @sql_colname = N'select @colname = Result' + @s + 'Name
        from [TestTypes]
        where [Type] = ' + @s_type;

    EXEC SP_EXECUTESQL 
        @Query = @sql_colname,
        @Params = N'@colname varchar(max) OUTPUT',
        @colname = @colname OUTPUT;

    -- (2-2) sql clause of the new column
    set @sql += ',
      cast(L.Result' + @s + ' as varchar(10)) + '' '' + R.Result' + @s + 'Unit as [' + @colname + ']'

    -- next Result
    set @i += 1
end

set @sql += '
    into [ans]
    from [Tests] as L
    inner join [TestTypes] as R
        on L.TestType = R.Type
    where R.[Type] = ' + @s_type;

/* execute */

print @sql;  -- check the query string

if OBJECT_ID('testdb..ans') is not null
    drop table testdb..ans;
exec sp_sqlexec @sql;

/* show */
select * from [ans];

Result (type = 1)

| ID   | Temperature | Variance |
|------|-------------|----------|
| 1001 | 50 F        | 29 %     |
| 1004 | 20.2 F      | 30 %     |

/* the query string */

select
    L.[ID],
      cast(L.Result1 as varchar(10)) + ' ' + R.Result1Unit as [Temperature],
      cast(L.Result2 as varchar(10)) + ' ' + R.Result2Unit as [Variance]
into [ans]
from [Tests] as L
inner join [TestTypes] as R
    on L.TestType = R.Type
where R.[Type] = 1

Tested on SQL Server 2017 (linux docker image, latest version) on debian 10