Insert column alias from table to query results

Question

I have two tables the A1 stores a criteria discription mapping and the B1 stores values from each criteria. I want a query (result query) to B1 table with the column aliases from A1 mapping like as described in the image below

Answer 1

I suggest you to change the design of the tables. This is the bad way to store data and it doesn't follow normalization rules. You need to have another junction table that would store CR values for the rownum.

Anyway, if you need to write query for these tables, then join A1 table to B1 table 3 times and get descr from there.

UPDATE (also used the PIVOT query from @gotqn answer):

As you said that there could be lots of CR* columns then... I hate cursors, but as you need it:

DECLARE @columnName VARCHAR(200),
        @query VARCHAR(8000) = 'SELECT b.rownum ',
        @joins VARCHAR(8000) = '',
        @tmpColumnValue VARCHAR(100);
DECLARE @getDescr CURSOR
SET @getDescr = CURSOR FOR
select name
from sys.columns where object_name(object_id) = 'B1' ANd name like 'CR%'
OPEN @getDescr
FETCH NEXT
FROM @getDescr INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @tmpColumnValue = pr_desc FROM A1 WHERE pr_id = @columnName;
    SET @query = @query + ', b.' + @columnName + ' AS ' + @tmpColumnValue;
PRINT @columnName
FETCH NEXT
FROM @getDescr INTO @columnName
END
CLOSE @getDescr
DEALLOCATE @getDescr

SET @query = @query + ' FROM B1 b ';
EXECUTE (@query)

Query with PIVOT:

 DECLARE @Descriptions VARCHAR(MAX);
 DECLARE @columns VARCHAR(MAX);
    SELECT @Descriptions = STUFF
    (
        (
        SELECT ',[' + [pr_desc] + ']'
        FROM A1
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1
        ,1
        ,''
    )

        SELECT @columns = STUFF
    (
        (
        SELECT ',[' + [name] + ']'
        FROM sys.columns
        WHERE object_name(object_id) = 'B1' ANd name like 'CR%'
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1
        ,1
        ,''
    )

  SET @query = '
    SELECT *
    FROM
    (
        SELECT [rownum]
              ,[value]
              ,[pr_desc]
        FROM B1
        UNPIVOT 
        (
            [value] FOR [column] IN (' + @columns + ')
        ) PVT
        INNER JOIN A1 DS
            ON PVT.[column] = DS.[pr_id]
    ) PVT
    PIVOT
    (
        MAX([value]) FOR [pr_desc] IN (' + @Descriptions + ')
    ) PVT;
    ';


EXEC (@query)

Answer 2

You can use this.

DECLARE @COLNAMES VARCHAR(4000) = 
    CONVERT(VARCHAR(4000), 
        (SELECT * FROM (VALUES(NULL) ) AS X(DUMY)
            LEFT JOIN B1 ON 1= 0 
                FOR XML RAW, ELEMENTS XSINIL ) ) 

SET @COLNAMES =  
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@COLNAMES,'<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">','')
    , '<DUMY xsi:nil="true"/>','')
    , ' xsi:nil="true"/><',', ') 
    , ', /row>','') 
    , '<','') 

SELECT 
    @COLNAMES = REPLACE(@COLNAMES, pr_id, pr_id + ' ['+ pr_desc+ ']') 
FROM A1

DECLARE @Query NVARCHAR(4000) = 'SELECT ' + @COLNAMES + ' FROM B1'  

PRINT @Query

EXEC sp_executesql @Query

Generated Query:

SELECT rownum, Name, Date, CR1 [Desc1], CR2 [Desc2], CR3 [Desc3] FROM B1

Result:

rownum      Name                                               Date                 Desc1       Desc2       Desc3
----------- -------------------------------------------------- -------------------- ----------- ----------- -----------
1           Jon                                                1/1/2017             10          50          100
2           Jon                                                2/1/2017             60          100         500

Answer 3

In the context of T-SQL this can be done following the steps below:

1. UNPIVOTING the data

   DECLARE @A1 TABLE
   (
       [pr_id] CHAR(3)
      ,[pr_desc] VARCHAR(12)
   );
   
   
   DECLARE @B1 TABLE
   (
       [rownum] TINYINT
      ,[name] VARCHAR(12)
      ,[Date] DATE
      ,[CR1] INT
      ,[CR2] INT
      ,[CR3] INT
   );
   
   INSERT INTO @A1 ([pr_id], [pr_desc])
   VALUES ('CR1', 'Desc1')
         ,('CR2', 'Desc2')
         ,('CR3', 'Desc3');
   
   INSERT INTO  @B1 ([rownum], [name], [Date], [CR1], [CR2], [CR3])
   VALUES (1, 'Jon', '1/1/2017', 10, 50, 100)
         ,(2, 'Jon', '2/1/2017', 60, 100, 500);
   
   SELECT *
   FROM @B1
   UNPIVOT 
   (
       [value] FOR [column] IN ([CR1], [CR2], [CR3])
   ) PVT;
   

   

2. Joining the @A1 table to get the descriptions:

   SELECT *
   FROM @B1
   UNPIVOT 
   (
       [value] FOR [column] IN ([CR1], [CR2], [CR3])
   ) PVT
   INNER JOIN @A1 DS
       ON PVT.[column] = DS.[pr_id];
   

   

3. PIVOTING the data again (note, we are selecting only the needed columns)

   SELECT *
   FROM
   (
       SELECT [rownum]
             ,[value]
             ,[pr_desc]
       FROM @B1
       UNPIVOT 
       (
           [value] FOR [column] IN ([CR1], [CR2], [CR3])
       ) PVT
       INNER JOIN @A1 DS
           ON PVT.[column] = DS.[pr_id]
   ) PVT
   PIVOT
   (
       MAX([value]) FOR [pr_desc] IN ([Desc1], [Desc2], [Desc3])
   ) PVT;   
   

   

As you can see, in order to use UNPIVOT and PIVOT we have a lot of hard coded values. So, it will be better to use dynamic T-SQL instead.

    CREATE TABLE A1
    (
        [pr_id] CHAR(3)
        ,[pr_desc] VARCHAR(12)
    );


    CREATE TABLE B1
    (
        [rownum] TINYINT
        ,[name] VARCHAR(12)
        ,[Date] DATE
        ,[CR1] INT
        ,[CR2] INT
        ,[CR3] INT
    );

    INSERT INTO A1 ([pr_id], [pr_desc])
    VALUES ('CR1', 'Desc1')
           ,('CR2', 'Desc2')
          ,('CR3', 'Desc3');

    INSERT INTO  B1 ([rownum], [name], [Date], [CR1], [CR2], [CR3])
    VALUES (1, 'Jon', '1/1/2017', 10, 50, 100)
          ,(2, 'Jon', '2/1/2017', 60, 100, 500);


    DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
    DECLARE @Descriptions NVARCHAR(MAX);

    SELECT @Descriptions = STUFF
    (
        (
        SELECT ',[' + [pr_desc] + ']'
        FROM A1
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1
        ,1
        ,''
    )

    SET @DynamicTSQLStatement = '
    SELECT *
    FROM
    (
        SELECT [rownum]
              ,[value]
              ,[pr_desc]
        FROM B1
        UNPIVOT 
        (
            [value] FOR [column] IN ([CR1], [CR2], [CR3])
        ) PVT
        INNER JOIN A1 DS
            ON PVT.[column] = DS.[pr_id]
    ) PVT
    PIVOT
    (
        MAX([value]) FOR [pr_desc] IN (' + @Descriptions + ')
    ) PVT;
    ';

    EXEC sp_executesql @DynamicTSQLStatement;

    DROP TABLE A1;
    DROP TABLE B1;