Преобразование одной строки с динамическим количеством столбцов в один столбец в Oracle

Мне нужно преобразовать одну строку, полученную с помощью оператора select, в один столбец с конкатенированными значениями отдельных столбцов результата. Проблема в том, что столбцы неизвестны и могут различаться по количеству.

Допустим, таблица выглядит примерно так:

Table USER
Name  Surname  Age  Logindate  City

Max   Smith    25   20.05.20   NY

Мне нужно SELECT * FROM USER и преобразовать результат в одну строку, например Max, Smith, 25, 20.05.20, NY, или с именами столбцов Name: Max, Surname: Smith, Age: 25, Logindate: 20.05.20, City: NY, которые я могу впоследствии вставить в столбец другой таблицы. Имя таблицы, из которой я выбираю, известно и жестко запрограммировано в операторе SELECT, который выполняется внутри хранимой процедуры.

Поскольку количество столбцов и имена столбцов неизвестны, я не могу использовать функцию CONCAT. Я также собирался удовольствоваться выходным форматом SELECT JSON_OBJECT(*) FROM USER, но функция с таким использованием звездочного оператора не поддерживается в Oracle18c (есть в Oracle19c).

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


sql oracle oracle18c
person Chilcone    schedule 20.05.2020    source источник
comment
Вы получаете данные из 1 таблицы за раз? или из запросов, которые также имеют несколько таблиц?   -  person Srinika Pinnaduwage    schedule 21.05.2020
comment
Если это одна таблица, знаете ли вы имя таблицы перед запуском запроса?   -  person Srinika Pinnaduwage    schedule 21.05.2020
comment
Какой инструмент генерирует оператор SELECT? Вместо создания сверхуниверсального оператора SELECT обычно проще использовать словарь данных для создания правильного оператора SQL. Например, вы можете прочитать из ALL_TAB_COLUMNS и создать список столбцов для SELECT.   -  person Jon Heller    schedule 21.05.2020
comment
Я использую одну таблицу за раз, и я знаю имя таблицы. Оператор SELECT запускается внутри хранимой процедуры и создается вручную (жестко запрограммирован). Я обновил свой вопрос.   -  person Chilcone    schedule 21.05.2020

Ответы (1)


arrow_upward
0
arrow_downward

Используйте словарь данных, чтобы сгенерировать правильный оператор SQL, а затем используйте динамический SQL для его выполнения.

--Sample tables for input and output:
create table user_table as
select 'Max' Name, 'Smith' Surname, 25 Age, date '2020-05-20' LoginDate, 'NY' City
from dual;

create table concatenated_values(value varchar2(4000));


--Procedure to read all columns from USER_TABLE and write them to CONCATENATED_VALUES.
create or replace procedure concatenate_values(p_table_name varchar2) is
    v_sql varchar2(4000);
begin
    --Generate a SQL statement to concatenate all the values.
    select
        'select ' ||
        listagg(column_name, '||'',''||') within group (order by column_id) ||
        ' from ' || owner || '.' || table_name
    into v_sql
    from all_tab_columns
    where owner = user
        and table_name = p_table_name
    group by owner, table_name;

    --Run the SQL statement and insert the value.
    execute immediate 'insert into concatenated_values ' || v_sql;
end;
/


--Call the procedure.
begin
    concatenate_values('USER_TABLE');
end;
/


--Results:
select * from concatenated_values;

VALUE
-------------------------
Max,Smith,25,20-MAY-20,NY
person Jon Heller    schedule 21.05.2020