Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Are all the "'||','||'"' stuff your attempt at string concatenation? –  Aaron Bertrand Sep 11 '13 at 19:46
    
Thanks for your replies so far. Let me explain further. The output data is correctly formatted. The problem is the header. I have an example SQL query from another application that I believe created the first header. When I use it, it creates the second header with pipes. I'd like to know how to create the first header with or without pipes. If there is no way, I can just output the bad header with good data then use 'sed' to remove the bad header and include a manually typed good header. –  MoChaMan Sep 11 '13 at 20:00
add comment

1 Answer

up vote 3 down vote accepted

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" |
share|improve this answer
    
The first select doesn't need all the concat stuff. it's just a single string - can be reduced to just '"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","‌​AGREEMENT_ID"' seeing as it's just 1 string column that's being returned –  Phil Sep 11 '13 at 23:33
    
@Phil Ah, you are right, fixed. thanks –  bluefeet Sep 11 '13 at 23:39
    
Thanks for this very fluent solution. The minor issue is the "MYCOL" that is still printed at the top of the output. This also happens on the actual database I'm working on. I'm going to thank you for this work and finesse the final details privately. –  MoChaMan Sep 12 '13 at 14:20
    
@MoChaMan Mycol is the name of the column, you can try using an empty space -- see this demo -- sqlfiddle.com/#!4/b1768/10 –  bluefeet Sep 12 '13 at 14:21
    
@bluefeet : I will use the " " name. My apologies about this basic question. I'm a Linux Systems Engineer trying to reverse engineer SQL and our DBA is ... "busy". Thanks again. –  MoChaMan Sep 12 '13 at 14:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.