SandPiper SandPiper - 3 months ago 12
SQL Question

Concatenating All Columns of Each Record Into One Entry

I am trying to extract the records of a table into a csv file. The SQL query is as simple as

SELECT * FROM MY_TABLE_NAME


which gives a hypothetical record set of

A B C D
E F G H
I J K L


What I want is a record set where all the columns are concatenated together, e.g.

A, B, C, D
E, F, G, H
I, J, K, L


I have been exposed to LISTAGG, but I do not want to aggregate records. I want to maintain each individual line entry's structure, just separated with a delimiter. Any thoughts?

EDIT:

I need to apply the query to multiple tables where I do not know what the number of columns is. Additionally, the table structures periodically change, and I need it to be flexible enough to account for that.

Answer

After a lot more work on this, I came up with an answer. Many thanks to help provided in this different, but related thread: How to Refer to a Column by ID or Index Number

Bottom line: I created a query with dynamic SQL then ran it with EXECUTE IMMEDIATE. The results were looped through and output one by one. It was a much more elegant solution.

DECLARE
    j    number := 0;
    sql_query    varchar2(32000);
    l_tableheaders    varchar2(32000);
    TYPE array_type IS TABLE OF varchar2(200) NOT NULL index by binary_integer;
    Data_Array array_type;
    MyTableName := 'TableName';

BEGIN
    SELECT LISTAGG(column_name, ' || '','' || ') WITHIN GROUP (ORDER BY column_id)
        INTO l_tableheaders FROM all_tab_cols WHERE table_name = MyTableName;

    sql_query := ' SELECT ' || l_tableheaders || ' FROM ' || MyTableName;

    EXECUTE IMMEDIATE sql_query BULK COLLECT INTO Data_Array;

    FOR j in 1..Data_Array.Count
    LOOP
        DBMS_OUTPUT.PUT ( Data_Array(j) );
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;

END;
Comments