Home » SQL & PL/SQL » Client Tools » sql plus report format problem
sql plus report format problem [message #245417] Sat, 16 June 2007 16:53 Go to next message
NIckman
Messages: 64
Registered: May 2007
Member
Hi
I am running sql select query send the results into excel.
My column are name,status,begin,end
i need the output like this. no space betwwen column output
name has varchar 200 in table def
satus has varchar 50
like that.
i need output should be like this

aaaaaaa,bbbbb,ccc,ddd
bbbb,zzz,xxx,yy
vvvvvvvvv,jjjjjjjjjjj,lllllll

i used colsep ","
still i am gettin spaces between columns
pl help
thx, N.

Re: sql plus report format problem [message #245418 is a reply to message #245417] Sat, 16 June 2007 21:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
You need to concatenate the values:


SELECT name || ',' || status || ',' || begin || ',' || end
FROM your_table;


If your values have trailing spaces, then you may also want to trim them:

SELECT RTRIM (name) || ',' || RTRIM (status) || ',' || RTRIM (begin) || ',' || RTRIM (end)
FROM your_table;

Re: sql plus report format problem [message #245471 is a reply to message #245418] Sun, 17 June 2007 15:26 Go to previous messageGo to next message
NIckman
Messages: 64
Registered: May 2007
Member
Thanks for your reply.
I have sql terminator ";" this symbol
yes i have trailing spaces.
i am sorry i want output like his

aaaaaa;bbb;bbbb;
zzzzzzzzzzz;bbbbbbb;mmmmm;
zz;yy;iiiiiiiiiiiiiiiiiiiiiiii

that means i have to sue colsep ";".
change the sql terminator from ; to some thisng else.

is this corect
thx

N.
Re: sql plus report format problem [message #245474 is a reply to message #245471] Sun, 17 June 2007 15:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara already answered your question:
Quote:
If your values have trailing spaces, then you may also want to trim them:

SELECT RTRIM (name) || ',' || RTRIM (status) || ',' || RTRIM (begin) || ',' || RTRIM (end)
FROM your_table;

Just change ',' to ';' and try it.

Regards
Michel
Re: sql plus report format problem [message #245475 is a reply to message #245471] Sun, 17 June 2007 16:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
The colsep is irrelevant when you concatenate all of the column values into one column. If you do not concatenate, as you previously noted, the columns are aligned, so that you get spaces between the column values. If you trim the values and concatenate them together into one column, then you eliminate the spaces. If you want a semicolon between values instead of a comma, then concatenate a semicolon instead of a comma, like so:

SELECT RTRIM (name) || ';' || RTRIM (status) || ';' || RTRIM (begin) || ';' || RTRIM (end)
FROM your_table;

Just try running the query to see what you get. The semicolon sql terminator is not affected by concatenating the semicolons between the values.
Re: sql plus report format problem [message #245625 is a reply to message #245417] Mon, 18 June 2007 04:25 Go to previous message
NIckman
Messages: 64
Registered: May 2007
Member
THanks for your help.
N.
Previous Topic: ORA-01012: not logged on
Next Topic: Accessing data in sqlserver 2000
Goto Forum:
  


Current Time: Tue Dec 06 08:52:11 CST 2016

Total time taken to generate the page: 0.17657 seconds