Home » SQL & PL/SQL » SQL & PL/SQL » doubt in writing query (11g)
doubt in writing query [message #593516] Sun, 18 August 2013 02:21 Go to next message
alokmishra
Messages: 3
Registered: August 2013
Location: Bangalore
Junior Member

Hi All,
I have a doubt in writing a block to extract data from a table in below mentioned scenario.

I have a table suppose "employee" with two columns "employee_group" and "employee".

so the table is storing like this

employee_group employee
A1 guru
A2 john
A2 sunny
A1 Ramesh

suppose in each group 1000 users are there, i want to know how can i write a block or a query so that every line it will show 10 users with comma separated
for a employee group and again next 10 users comma separated for the same group until it reaches 1000 users.

please help me on this.
Re: doubt in writing query [message #593517 is a reply to message #593516] Sun, 18 August 2013 02:27 Go to previous messageGo to next message
alokmishra
Messages: 3
Registered: August 2013
Location: Bangalore
Junior Member

output should be like this
employee_group employee

A1 alok,guru,ramesh,prabhu,john,stella,len,penny,christina,tripti
A1 mahesh,crook,jack,james,maya,alisa,maria,mellisa,pratik,som
A1 ............................................................
................................................................................
A2 ............................................................
A2 ............................................................
Re: doubt in writing query [message #593518 is a reply to message #593517] Sun, 18 August 2013 02:32 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Provide your CREAET TABLE statement and INSERT statements with enough values (more than just two rows) to build a test case.

What is your oracle version? (4 digits)

Regards,
Dariyoosh
Re: doubt in writing query [message #593519 is a reply to message #593516] Sun, 18 August 2013 02:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
Registered: May 2013
Location: World Wide on the Web
Senior Member
Since your Oracle version is 11g, you could use LISTAGG function introduced in 11g.

It is infact FAQ, please search the forum for LISTAGG.

[Updated on: Sun, 18 August 2013 02:40]

Report message to a moderator

Re: doubt in writing query [message #593521 is a reply to message #593519] Sun, 18 August 2013 02:48 Go to previous messageGo to next message
alokmishra
Messages: 3
Registered: August 2013
Location: Bangalore
Junior Member

I am using listagg but how can fetch 10 users every line.
Re: doubt in writing query [message #593522 is a reply to message #593521] Sun, 18 August 2013 03:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
Registered: May 2013
Location: World Wide on the Web
Senior Member
Dariyoosh asked to post some information.

Along with that, also post what you have done, use SQL*Plus to copy paste the output.

[Updated on: Sun, 18 August 2013 03:04]

Report message to a moderator

Re: doubt in writing query [message #593526 is a reply to message #593516] Sun, 18 August 2013 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:
I am using listagg but how can fetch 10 users every line.


Number your rows for each employee using ROW_NUMBER and also group by trunc(this number/10).

Regards
Michel

[Updated on: Sun, 18 August 2013 03:57]

Report message to a moderator

Re: doubt in writing query [message #593533 is a reply to message #593526] Sun, 18 August 2013 04:57 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Michel Cadot wrote on Sun, 18 August 2013 10:57
Number your rows for each employee using ROW_NUMBER and also group by trunc(this number/10).


I would rather say ceil(this number/10) otherwise the first group within each user group will have 9 users and not 10.

SQL> SELECT trunc(9 / 10) FROM "PUBLIC".dual;

TRUNC(9/10)
-----------
	  0

SQL> SELECT trunc(10 / 10) FROM "PUBLIC".dual;

TRUNC(10/10)
------------
	   1

SQL>


Which means that the 9th and 10th user will not be in the same group. But using ceil they will be in the same group

SQL> SELECT ceil(9 / 10) FROM "PUBLIC".dual;

CEIL(9/10)
----------
	 1

SQL> SELECT ceil(10 / 10) FROM "PUBLIC".dual;

CEIL(10/10)
-----------
	  1

SQL>


Regards,
Dariyoosh
Re: doubt in writing query [message #593539 is a reply to message #593533] Sun, 18 August 2013 06:20 Go to previous message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would rather say ceil(this number/10) otherwise the first group within each user group will have 9 users and not 10.


This was just a hint not a complete solution. In addition "this number" does not mean the result of ROW_NUMBER, I though to use row_number+1 as I used to do it. Only stupid people won't see there is not the same number of items in the list of names. It is useless to argument and to post a so long post for such a tiny point.

Regards
Michel

[Updated on: Sun, 18 August 2013 08:39]

Report message to a moderator

Previous Topic: meaning of "partition by null" in analytic functions
Next Topic: Help in using record type and object type
Goto Forum:
  


Current Time: Sun Oct 26 01:01:39 CDT 2014

Total time taken to generate the page: 0.08370 seconds