Oracle: SQLPlus Query Concatenate Data With Headers

Question

I'm trying to not only query different tables in my database and export them into a CSV, but also export data from multiple tables and concatenate them as columns into this CSV.

I start with the premise of being able to spool the contents of a table into a CSV file:

set colsep ','
set echo off
set feedback off
set linesize 1000
set sqlprompt ''
set headsep off
spool file.csv
select * from messages;

Now I want to grab certain components of multiple tables with no avail (with union and minus):

select col1, col2, col3 from messages
union
select col1, col2 from messagedata
union
select col1 from extradata;

In trying the above method, I'll get ORA -1789 query block has incorrect number of result columns meaning that I'm trying to match up the wrong number of columns (which I'm trying to avoid by just appending columns in my output).

And given either union or minus it will error on the wrong datatype, but that being because it's just trying to append it to the columns from my first query.

My goal there is to create a query that produces similar output:

| col1(messages)  | ... |  col2(messagedata) | ... |  col3(extradata) |
-----------------------------------------------------------------------
  message         ,...    ,messagedata        ,...   ,extradata      
  message         ,...    ,messagedata        ,...   ,extradata
  message         ,...    ,messagedata        ,...   ,extradata

So two questions:

• Is it possible for me to concatenate the data from multiple tables into a column output like above? As in create new columns to output instead of appending to existing columns?

• How might I give a header to my data (custom or already defined by the identifier in the table) at the top of the column?

Answer 1

IMHO you either join your tables if they're related to each other. One way to do it if col1 is the common column

SELECT COALESCE(m.col1, d.col1, e.col1) column_name1,
       m.col2 column_name2,
       m.col3 column_name3,
       d.col2 column_name4,
       e.col2 column_name5
  FROM messages m FULL JOIN messagedata d
    ON m.col1 = d.col1 FULL JOIN extradata e
    ON d.col1 = e.col1

Sample output:

| COLUMN_NAME1 | COLUMN_NAME2 | COLUMN_NAME3 | COLUMN_NAME4 | COLUMN_NAME5 |
|--------------|--------------|--------------|--------------|--------------|
|            1 |      value12 |      value13 |        data1 |   extradata1 |
|            2 |      value21 |      value31 |        data2 |   extradata2 |

or you UNION them in this way

SELECT col1 column_name1, col2 column_name2, col3 column_name3
  FROM messages
 UNION ALL
SELECT col1, col2, NULL
  FROM messagedata
 UNION ALL
SELECT col1, NULL, NULL
  FROM extradata

Note that the number of columns and their respective data types should be the same for all SELECTs that you UNION. To overcome this you can inject constant values (e.g. NULL) and convert/cast column values to some common type (e.g. VARCHAR2) if needed.

Sample output:

| COLUMN_NAME1 | COLUMN_NAME2 | COLUMN_NAME3 |
|--------------|--------------|--------------|
|            1 |      value12 |      value13 |
|            2 |      value21 |      value31 |
|            1 |        data1 |       (null) |
|            2 |        data2 |       (null) |
|            1 |       (null) |       (null) |
|            2 |       (null) |       (null) |

Here is SQLFiddle demo