Oracle SQL: Rolling up by data by Primary Key SQL syntax in SQL developer

Here is the output (pipe delimited) I am looking for (the first row is a header)

Order Number|Book Names
100|[Book1, Book12, Book11]
200|[Book9, Book1]
300|[Book3]
400|[Book11, Book10, Book2, Book4]


Where 'Order Number' is the first column in tab1 table.
The second column 'Book Names' is a roll-up of all the book names for that order in brackets []

Sample table data:
/*
drop table tab1 purge;
create table tab1(ordNum number, line number, InventoryID number);

insert into tab1 values(100,1,100001);
insert into tab1 values(100,2,100012);
insert into tab1 values(100,3,100011);
insert into tab1 values(200,1,100009);
insert into tab1 values(200,2,100001);
insert into tab1 values(300,1,100003);
insert into tab1 values(400,1,100011);
insert into tab1 values(400,2,100010);
insert into tab1 values(400,3,100002);
insert into tab1 values(400,4,100004);


drop table tab2 purge;
create table tab2(InventoryID number, InventoryName varchar2(20));


insert into tab2 values(100001,'Book1');
insert into tab2 values(100002,'Book2');
insert into tab2 values(100003,'Book3');
insert into tab2 values(100004,'Book4');
insert into tab2 values(100005,'Book5');
insert into tab2 values(100006,'Book6');
insert into tab2 values(100007,'Book7');
insert into tab2 values(100008,'Book8');
insert into tab2 values(100009,'Book9');
insert into tab2 values(100010,'Book10');
insert into tab2 values(100011,'Book11');
insert into tab2 values(100012,'Book12');
commit;
*/
Newbie345Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Based on your previous questions, you might have problems here since this also uses SPOOL and listagg.

What is your database version?

set pages 0
set lines 10000
set feedback off
spool myfile.txt
prompt Order Number|Book Names
select ordnum || '|[' || listagg(InventoryName,',') within group(order by t1.inventoryid) || ']'
from tab1 t1 join tab2 t2 on t1.inventoryid=t2.inventoryid
group by ordnum
/
spool off

Open in new window

0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
If you cannot use LISTAGG, here is an XML version:
set pages 0
set lines 10000
set feedback off
spool myfile.txt
prompt Order Number|Book Names
select ordnum || '|[' ||
RTRIM(
           EXTRACT(XMLAGG(XMLELEMENT("s", t2.inventoryname || ',')), '/s/text()').getstringval(),
           ','
       )
|| ']'
from tab1 t1 join tab2 t2 on t1.inventoryid=t2.inventoryid
group by ordnum
/
spool off

Open in new window

0
 
Newbie345Author Commented:
Thanks slightwv. I will be using SSIS for routing the output from the SQL query to a text file so spool might no longer be an issue here. I am using Oracle SQL developer 2.1.1.64. I will test this syntax sometime later today and will get back to you tomorrow. Thanks again for the prompt help. I appreciate it.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Newbie345Author Commented:
Slightwv,

For some reason, I am getting the following error

ORA-00923: FROM keyword not found where expected
00923 00000 - "FROM keyword not found where expected"
*Cause
*Action:

Can you send the query that I can run in the SQL developer directly (not from SQL PLUS)
0
 
Newbie345Author Commented:
Is it possible to achieve this using a while loop?
0
 
slightwv (䄆 Netminder) Commented:
>>For some reason, I am getting the following error

Execute it as a script.

>>Is it possible to achieve this using a while loop?

Possibly but what would the result be returned as if you use PL/SQL?

If you want the file on the database server you can create a stored procedure and use UTL_FILE to write the data to a file.  There are TONs of examples of UTL_FILE.  Jsut use the query I provided in a loop.

>>I will be using SSIS for routing the output from the SQL query to a text file

I think you mentioned this in your last question.  Why would you want to use a non-Oracle product to do something that Oracle will easily do out of the box?
0
 
Newbie345Author Commented:
I am not sure about the background but there have been several queries scheduled through SSIS already. So I was asked to develop this query using SSIS package as well.

Is there a way to just get the SQL query for this (rather than developing a stored procedure which I am not allowed to)?
0
 
slightwv (䄆 Netminder) Commented:
I gave you the SQL query.  I actually gave you two.  One using LISTAGG and one using an XML trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.