Home » SQL & PL/SQL » SQL & PL/SQL » print in one line or in one variable (Oracle 10g)
print in one line or in one variable [message #406281] Wed, 03 June 2009 02:12 Go to next message
McLan
Messages: 36
Registered: April 2008
Member
Hi,

I would like to print all emp_ids in one line or want to fetch one string variable with comma separated. For example like below:

1,2,3,4,6,9

How to achieve the same?

Thanks,
Re: print in one line or in one variable [message #406288 is a reply to message #406281] Wed, 03 June 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is asked every week.
Search for "pivot".

Regards
Michel
Re: print in one line or in one variable [message #406303 is a reply to message #406288] Wed, 03 June 2009 04:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or Stragg
Re: print in one line or in one variable [message #406340 is a reply to message #406281] Wed, 03 June 2009 07:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Just a thought here...what if the table had thousands of rows? Would you still want to display one million values separated by comma?
Re: print in one line or in one variable [message #406345 is a reply to message #406281] Wed, 03 June 2009 08:27 Go to previous messageGo to next message
McLan
Messages: 36
Registered: April 2008
Member
I have looked at all the links for pivot and Stragg, These solutions will work for fixed number of rows with hard coding of decoding.
I would like to know something that works for thousands of rows.
I would like to print only values of column emp_ids from 1 to n rows ( where n can be 100, 1000. of Corse there could be some missing numbers) or at least range from 100 to 1000.

All employees list: 1,2,3, 6 .....1000

Is it possible by only with cursor, in the loop I should get the emp_id and concat to the buffer with comma?

Thanks
Re: print in one line or in one variable [message #406346 is a reply to message #406345] Wed, 03 June 2009 08:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You've obviously not looked very carefully - STRAGG is a aggregation function, and will work with an unlimited number of rows (up to the limits of the datatype)
Re: print in one line or in one variable [message #406378 is a reply to message #406346] Wed, 03 June 2009 13:52 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
sys_connect_by_path (9i or later) may be the easiest. Unsure which solution is most performant for large results though...

  SELECT     cnt, LTRIM (SYS_CONNECT_BY_PATH (object_id, ','), ',') ids
      FROM (SELECT object_id, ROW_NUMBER () OVER (ORDER BY object_id) rn,
                   COUNT (*) OVER () cnt
              FROM user_objects
             WHERE object_id IS NOT NULL)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR 1 = 1 AND PRIOR rn = rn - 1
  ORDER BY 1


CNT	IDS
185	23016,23017,23018,23019,23020,23021,...



Previous Topic: pl/sql with ftp
Next Topic: Select for update
Goto Forum:
  


Current Time: Sat Dec 10 18:42:50 CST 2016

Total time taken to generate the page: 0.07962 seconds