SQL output formatting

formatoracle-11g-r2selectsqlplus

I want this:

"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID"
"abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos"

But I get this:

'"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"MINOR_VERSION"||'"'||','||'"'||"COUNTRY"||'"'||','||'"'||"ACCEPTANCE_TIMESTAMP"||'"'||','||'"'||"AGREEMENT_ID"||'"'
"abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos"

Using the following SQL:

select '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN' and rownum < 10;

How can I get the first output ?

Best Answer

I am going to go out on a limb here and try and answer even though you haven't provided a lot of details.

It looks like you want to create a comma-separated list of the columns and then the values below it but in sqlplus you are getting the column header as the actual SQL string. If I were to use the following in a SQL Fiddle I will get the same result:

select 
  '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN' 
and rownum < 10;

See Demo. And the result is two rows, one with the column name but in this case it is the actual sql that we ran and then the row of data comma separated:

| '"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"MINOR_VERSION"||'"'||','||'"'||"COUNTRY"||'"'||','||'"'||"ACCEPTANCE_TIMESTAMP"||'"'||','||'"'||"AGREEMENT_ID"||'"' |
|                                                                                                       "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" |

Now if I want to create the column headers as a row, I would alter the query to use a UNION ALL, the first query just creates a string of the column headers:

select 
  '"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID"' mycol
from dual
union all
select 
  '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN' 
and rownum < 10;

See Demo. This gives me a result:

|                                                                                       MYCOL |
| "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" |
|             "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" |
Related Question