How can I select table 1 columns with reference ItemName from table 2?

Final Table

We tried with PIVOT on table 2 and LEFT JOIN.

CodePudding user response：

Your data

Declare @t1 table(
   RowID INTEGER  NOT NULL 
  ,A1    float  NOT NULL
  ,A2    float NOT NULL
  ,A3    float NOT NULL
);
INSERT INTO @t1
  (RowID,A1,A2,A3) VALUES 
  (1,1,1.0,0.1),
  (2,2,2.0,0.2);

Declare @t2 table(
   Item_Name     VARCHAR(60) NOT NULL 
  ,Cell_location VARCHAR(20) NOT NULL
);
INSERT INTO @t2(Item_Name,Cell_location) VALUES 
('Item1','A1'),
('Item2','A3'),
('Item3','A2');

what you need, first Unpivot first table and then Join it with second table and then use Pivot table

select 
  * 
from 
  (
    select 
      RowID, 
      Item_Name, 
      value 
    from 
      (
        select 
          RowID, 
          name, 
          value 
        from 
          @t1 unpivot (
            value for name in ([A1], [A2], [A3])
          ) unpiv
      ) a 
      join @t2 t2 on a.name = t2.Cell_location
  ) b pivot (
    sum(value) for Item_Name in ([Item1], [Item2], [Item3])
  ) piv;

Dbfiddle