Home » SQL & PL/SQL » SQL & PL/SQL » Select results in a single row
Select results in a single row [message #191105] Mon, 04 September 2006 11:14 Go to next message
123soleil
Messages: 35
Registered: July 2006
Member
Hello,

I would like to perform a simple select and have all the results in a single row.

eg: select id, name from employees

id..............name
------------------------------
1,2,3...........smith,johnson,rodrigez

returning the results in two variables would also be ok, as long as they are strings, not arrays (ie id='1,2,3' name='smith,johnson,rodrigez'). I can't find a simple solution (found complicated solutions but couldn't get them to work for my simple problem)

Thanks!

[Updated on: Mon, 04 September 2006 11:14]

Report message to a moderator

Re: Select results in a single row [message #191110 is a reply to message #191105] Mon, 04 September 2006 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
google "pivot query"
Re: Select results in a single row [message #191114 is a reply to message #191105] Mon, 04 September 2006 12:38 Go to previous messageGo to next message
123soleil
Messages: 35
Registered: July 2006
Member
thanks, but I don't think that's what I need... I basically need to put every result from a column in a single cell or in a variable (for example in a CSV format). No groupings or whatever.

But maybe it is what I am looking for, could you give me an example of pivot tables using my example?

thanks!
Re: Select results in a single row [message #191120 is a reply to message #191105] Mon, 04 September 2006 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
How will you know when the 1st column of data ends & the 2 column begins?
How do you discern the number of rows & columns in the table once it is one LONG jumble of characters?
Re: Select results in a single row [message #191137 is a reply to message #191120] Tue, 05 September 2006 00:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do a search for CONCAT_ALL on this forum.

Or something like this would work on 10g:

SELECT sys_connect_by_path(empno, ',') AS empno
, sys_connect_by_path(ename, ',') AS ename
FROM (
  SELECT empno, ename, ROWNUM AS rn
  FROM emp
)
WHERE connect_by_isleaf = 1
CONNECT BY LEVEL = rn
START WITH rn = 1


You could also get this to work on 9i using a sub-query instead of the CONNECT_BY_ISLEAF pseudocolumn.

Ross Leishman
Re: Select results in a single row [message #191152 is a reply to message #191137] Tue, 05 September 2006 01:20 Go to previous message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Try a search for "stragg" in Tom Kyte's site.

Wink

[Updated on: Tue, 05 September 2006 01:20]

Report message to a moderator

Previous Topic: untrim column name
Next Topic: LANGUAGE SETTING
Goto Forum:
  


Current Time: Fri Dec 09 08:14:04 CST 2016

Total time taken to generate the page: 0.09948 seconds