Convert a single row with dynamic number of columns into a single column in Oracle

user_3156986 0
Viewed 115

I have to convert a single row that is obtained via select statement into a single column with concatenated values of the individual columns of the result. The problem is that the columns are unknown and can vary in number.

Let's say the table looks similar to this:

Table USER
Name  Surname  Age  Logindate  City

Max   Smith    25   20.05.20   NY

I need to SELECT * FROM USER and convert the result into a single string like Max, Smith, 25, 20.05.20, NY or with column names Name: Max, Surname: Smith, Age: 25, Logindate: 20.05.20, City: NY that I can afterwards insert into a column of other table. The name of the table that I'm selecting from is known and hardcoded into the SELECT statement that is executed inside a stored procedure.

Since the number of columns and column names are unknown, I cannot use a CONCAT function. I was also going to be satisfied with the output format of SELECT JSON_OBJECT(*) FROM USER, but the function with such usage of star operator is not supported in Oracle18c (it is in Oracle19c).

The transformation of column values of a single row into a single string seems like a basic operation, but I wasn't able to find any simple solution.

oracle18c oracle sql
1 Answers

Use the data dictionary to generate the right SQL statement and then use dynamic SQL to execute it.

--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
user_4091720
Related