CODEWITHSUNDEEP
sqloracle-databaseconcatenation
SandPiper
SandPiper

Reputation: 2906

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.

Upvotes: 4

Views: 3862

Answers (2)

SandPiper
SandPiper

Reputation: 2906

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Just use the concatenation operator:

select col1 || ', ' || col2 || ', ' || col3 || ', ' || col4
from t;

Upvotes: 4

Related Questions