Getting Table Column Value Based on Dynamic Column Name

I have a ‘column specifier’ table that specifies the names of 1 or more columns of a source table that contain the data needed for stuff. The list of columns required depends on various things, but here, it depends only on what I’m calling the ‘cid’. I must retrieve the values found in each column of the data table that is a column specified in the specifier, or column table, and do that for every row of the data table with a cid matching the cid in the column table. I have tried a few approaches, but the closest I’ve been is to create the dynamic SQL that retrieves one column at a time. The setup for this:

The specifier or column table is ColumnTable:

create table #ColumnTable (
    cid integer,
    columnName varchar(20),
    colValue integer);

insert into #ColumnTable (
    cid,
    columnName)
values
    (23, 'col1'),
    (23, 'col2'),
    (23, 'col3'),
    (34, 'col1'),
    (34, 'col3'),
    (43, 'col2'),
    (43, 'col1'),
    (43, 'col4'),
    (44, 'col2');

…and the source or data table is:

    create table #DataTable (
     cid  integer
   , col1 integer
   , col2 integer
   , col3 integer
   , col4 integer
   , col5 integer);

insert into #DataTable (
    cid
  , col1
  , col2
  , col3
  , col4
  , col5)
values
    (23, 1,   2,  3,  4, 37),
    (34, 7,  10, 13,  4, 3),
    (43, 5,   2,  4,  4, 35),
    (44, 19, 12, 13, 24, 53);

So, ‘col1’, ‘col2’ etc are the literal column names I’m told I need to get the row values from, for the row where ‘cid’ is as specified. There will be only one row per cid in the source data, potentially dozens of columns any one or more of which are my quarry. I can know the complete list of actual column names and their column sequence numbers for the data table, and I can get the same information from the specifier table. The final result is an updated colValue column of the column specifier table, which for this example is:

cid columnName  colValue
23  col1         1
23  col2         2
23  col3         3
34  col1         7
34  col3        13
43  col2         2
43  col1         5
43  col4         4
44  col2        12

I have used string_agg to aggregate the column names found in the specifier table, but I need to get those column values from the row of the data table where data.cid = column.cid, in part to ensure I can update the column table with the correct values. I have the added issue of the specifier’s column names being in rows. Any guidance appreciated, even if it’s a different way to think the problem.

You need to sign in to view this answers