SQL> select student_name, course_id from studentx order by student_name
STUDENT_NAME COURSE_ID
------------ ---------
Chris Jones A102
Chris Jones C102
Chris Jones C102
Chris Jones A102
Chris Jones A103
Chris Jones A103
Joe Rogers B103
Joe Rogers A222
Joe Rogers A222
Kathy Smith B102
Kathy Smith A102
Kathy Smith A103
Kathy Smith B102
Kathy Smith A103
Kathy Smith A102
Mark Robert B103
16 rows selected.
SQL> WITH x AS
(SELECT student_name,
course_id,
ROW_NUMBER () OVER (PARTITION BY student_name ORDER BY 1) AS grouprownum
FROM studentx)
SELECT student_name,
LISTAGG (CASE WHEN grouprownum < 5 THEN course_id ELSE NULL END, ',')
WITHIN GROUP (ORDER BY student_name)
courses
FROM x
GROUP BY student_name
STUDENT_NAME
------------
COURSES
--------------------------------------------------------------------------------
Chris Jones
A102,A102,C102,C102
Joe Rogers
A222,A222,B103
Kathy Smith
A102,A103,B102,B102
Mark Robert
B103
4 rows selected.
Friday, August 14, 2015
Limit the number of list entries that listagg will consume.
Subscribe to:
Post Comments (Atom)
Labels
- aggregate (1)
- broken (1)
- cast (3)
- collect (4)
- collect detail (1)
- conditional constraint (1)
- CONNECT BY LEVEL (2)
- convert excel date to oracle date (1)
- Copy data from Salesforce to Oracle (1)
- Createuri (1)
- CSV (1)
- database link (1)
- db link (1)
- DBMS_OUTPUT (1)
- disable (1)
- distributed transaction (1)
- does not work (2)
- duplicate rows (1)
- dynamic cursor (1)
- enable (1)
- error (1)
- example (11)
- excel to oracle (1)
- Export (1)
- Extract (1)
- Extract from SalesForce (1)
- fails (1)
- file too large (1)
- Firefox (1)
- Firefox has blocked content that isn't secure (1)
- Firefox mixed content blocking (1)
- flagger (1)
- Function Based Index (1)
- generate csv (1)
- grouping (1)
- grouping sets (1)
- how to (1)
- http iframed in https (1)
- http inside https (1)
- Httpuritype (1)
- Import (1)
- incomplete (1)
- incremental elimination (1)
- limit (1)
- limit listagg (1)
- listagg (1)
- Load (1)
- Load to Oracle (1)
- merge limitation (1)
- merge problem (1)
- merge restriction (1)
- Migrate data from Salesforce to Oracle (1)
- mixed content (1)
- MODEL (2)
- nested table (1)
- not all variables bound (1)
- nvarchar2 (1)
- ora-01008 (1)
- ORA-01756 (1)
- ORA-06502 (1)
- ORA-12714 (1)
- Oracle (3)
- oracle client (1)
- oracle merge (1)
- Oracle Q-strings (1)
- Oracle SQL (6)
- Oracle SQL reporting functions (1)
- Package Structure (1)
- Partition By (1)
- PL/SQL (6)
- PL/SQL collection (1)
- PL/SQL forward declaration (1)
- PL/SQL overload (1)
- PL/SQL subprogram (1)
- powermultiset (2)
- predicate negation (1)
- problem (2)
- replicate rows (1)
- reversing where clause logic (1)
- rollup (1)
- SalesForce (1)
- Salesforce to Oracle (1)
- save detail (1)
- sequence of numbers (1)
- Set_transfer_timeout (1)
- simple (1)
- SP2-004 (1)
- SP2-0734 (1)
- sql (1)
- sqlblanklines (1)
- SQLPLUS (2)
- sqlplus multiline (1)
- sqlplus multiline text (1)
- TABLE function (2)
- to Oracle (1)
- trigger (1)
- trouble (1)
- UTL_HTTP (1)
- UTL_HTTP Set_transfer_timeout (1)
- Utl_url Escape (1)
- Windows 7 Search (1)
- Windows Search (1)
- XMLAGG (2)
- XMLELEMENT (2)
2 comments:
That's rather elegant, I love it. I've been having trouble with this recently and I might give it a go. Stupid 4000 character limit...
this so easy solution to max 4000 character, thanks lot
Post a Comment