Получение значения столбца таблицы на основе динамического имени столбца

Содержание

Вопрос или проблема
Ответ или решение
Шаги Решения
Пример Реализации
Пояснение Решения
Заключение

Вопрос или проблема

У меня есть таблица ‘спецификатор столбцов’, которая указывает имена одного или нескольких столбцов исходной таблицы, содержащих данные, необходимые для различных операций. Список требуемых столбцов зависит от различных факторов, но в данном случае он зависит только от так называемого ‘cid’. Я должен извлечь значения, найденные в каждом столбце таблицы данных, которые указаны в спецификаторе или таблице столбцов, и сделать это для каждой строки таблицы данных, где cid совпадает с cid в таблице столбцов. Я попробовал несколько подходов, но самый близкий из них – это создание динамического SQL, который извлекает один столбец за раз. Настройка для этого:

Спецификатор или таблица столбцов – это 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');

…а исходная или таблица данных:

    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);

Итак, ‘col1’, ‘col2’ и так далее – это буквальные имена столбцов, из которых мне нужно получить значения строк для строки, где ‘cid’ указан. Для каждого cid в исходных данных будет только одна строка, потенциально десятки столбцов, каждый из которых может быть предметом моего запроса. Я знаю полный список фактических имен столбцов и их номера последовательности для таблицы данных, а также могу получить ту же информацию из таблицы спецификатора. Окончательный результат – обновленный столбец colValue в таблице спецификатора столбцов, который для этого примера выглядит следующим образом:

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

Я использовал string_agg для агрегации имен столбцов, найденных в таблице спецификатора, но мне нужно получить эти значения столбцов из строки таблицы данных, где data.cid = column.cid, частично для того, чтобы убедиться, что я могу обновить таблицу столбцов с правильными значениями. У меня есть дополнительная проблема: имена столбцов спецификатора находятся в строках. Буду признателен за любые советы, даже если это другой способ взглянуть на проблему.

Ответ или решение

Для решения вашей задачи по извлечению значений столбцов на основе динамических имен столбцов, указанных в таблице спецификации, мы можем использовать динамический SQL в сочетании с курсорами или использовать универсальные конструкции SQL, такие как JOIN и CASE. Ниже представлено решение, которое включает использование динамического SQL как наиболее подходящий подход для обработки динамически определяемых имен столбцов.

Шаги Решения

Построение Динамического SQL
Мы создадим строку запроса, которая будет извлекать значения из исторической таблицы данных на основе имен столбцов, указанных в таблице спецификаций.
Использование CTE и Функции
Мы также можем использовать Common Table Expressions (CTE) для удобства чтения и структурирования запроса.
Использование EXEC для Исполнения Динамического SQL
Мы будем использовать EXEC для выполнения динамически созданного SQL-запроса.

Пример Реализации

Вот пример кода, который реализует предложенные шаги. В этом коде мы сначала создаем таблицы и заполняем их данными, а затем извлекаем значения.

-- Создаем таблицы
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');

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);

-- Общий запрос для получения значений
DECLARE @sql NVARCHAR(MAX)
DECLARE @colValue NVARCHAR(20)
DECLARE @cid INT

-- Создаем динамический SQL
SET @sql = '
    INSERT INTO #ColumnTable (cid, columnName, colValue)
    SELECT ct.cid, ct.columnName, dt.' + 
    (SELECT STRING_AGG(columnName, ' AS colValue, dt.') 
     FROM #ColumnTable ct 
     WHERE dt.cid = ct.cid) + 
    ' 
    FROM #DataTable dt 
    JOIN #ColumnTable ct ON dt.cid = ct.cid
WHERE ct.cid IS NOT NULL;'

EXEC sp_executesql @sql;

-- Проверяем результаты
SELECT * FROM #ColumnTable;

-- Удаляем временные таблицы
DROP TABLE #ColumnTable;
DROP TABLE #DataTable;

Пояснение Решения

Создание временных таблиц: Мы создали временные таблицы для хранения спецификаций колонок и данных.
Динамический SQL: Мы используем динамический SQL, чтобы построить запрос, который выполняет JOIN между #ColumnTable и #DataTable, после чего извлекает значения на основании динамически определяемых имен столбцов.
Исполнение запроса: Код выполняется с помощью EXEC sp_executesql, что позволяет нам исполнять динамически сформированный SQL-запрос.

Заключение

Предложенное решение позволяет гибко и эффективно извлекать данные из таблицы, используя динамические имена колонок. Этот подход обеспечивает простоту в использовании и поддерживает обновляемую структуру данных. Надеюсь, это решит вашу задачу и предоставит необходимые значения в соответствии с вашими требованиями.