OraFAQ Forum: Client Tools » sql plus report format problem

Home » Other » Client Tools » sql plus report format problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql plus report format problem [message #245417]

Sat, 16 June 2007 16:53

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.

Report message to a moderator

Re: sql plus report format problem [message #245418 is a reply to message #245417]

Sat, 16 June 2007 21:30

Barbara Boehmer
Messages: 9088
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;

Report message to a moderator

Re: sql plus report format problem [message #245471 is a reply to message #245418]

Sun, 17 June 2007 15:26

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.

Report message to a moderator

Re: sql plus report format problem [message #245474 is a reply to message #245471]

Sun, 17 June 2007 15:57

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: sql plus report format problem [message #245475 is a reply to message #245471]

Sun, 17 June 2007 16:02

Barbara Boehmer
Messages: 9088
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.

Report message to a moderator

Re: sql plus report format problem [message #245625 is a reply to message #245417]

Mon, 18 June 2007 04:25

NIckman
Messages: 64
Registered: May 2007

Member

THanks for your help.
N.

Report message to a moderator

Previous Topic:	ORA-01012: not logged on
Next Topic:	Accessing data in sqlserver 2000

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:13:16 CDT 2024