Aggregate result from Oracle request

Question

I have a table looking like this :

Table: table_name

name   priority    day       hour
-------------------------------------
name1     1       monday     21:00
name2     3       tuesday    21:00
name3     1       monday     21:00
name4     2       monday     21:00
name5     2       sunday     22:00
name6     1       sunday     23:00
name7     1       thursday   00:00
name8     2       sunday     22:00

Is someone as any idea how I can do a request, then manipulate the result to aggregate the result as below (without the column header of course) :

priority    day       hour          name
-----------------------------------------------
   1       monday     21:00      name1,name3
   3       tuesday    21:00         name2
   2       monday     21:00         name4
   2       sunday     22:00      name5,name8
   1       sunday     23:00         name6
   1       thursday   00:00         name7

I want to group by priority and day and hour.

Priority can be 1 to 5.
Day can obviously be Monday to Sunday
Hour can obviously be any hours :)
Name can be anything.

At the end, I need a SQL query to write the formatted results into a file.

What have you tried so far? Why didn't it work? Please [edit] the question to describe the problems you've encountered This isn't a code-writing service! — amon, Sep 07 '17 at 06:25

Gerhard · Accepted Answer · 2017-09-07T12:31:13.667

0

As a workaround for the ORA-01489 you can use XMLAGG as a workaround as per this answer

with extract

 SET LONG 2000000
 SET pagesize 50000
 SELECT rtrim(xmlagg(XMLELEMENT(e,text,',').EXTRACT('//text()')
                     ).GetClobVal(),',') very_long_text
  FROM
   (SELECT to_char(to_date(level,'j'), 'jsp') text FROM dual CONNECT BY LEVEL < 250
    )

edited Sep 07 '17 at 12:31

answered Sep 07 '17 at 06:49

Gerhard

22,678
7
27
43

Your mix of lower case only and mixed case variable names is strange. You should decide for one type and be consistent. The convention in Perl is to use snake case like `$query_db`. – simbabque Sep 07 '17 at 08:26
I'm just wondering why I have downvotes for the answer which returns the expected result for the question, whereas, the copy paste solution which does not return the proper answer from a different link is accepted ... – Radim Bača Sep 07 '17 at 17:54

Radim Bača · Answer 2 · 2017-09-07T06:40:44.380

-1

STRAGG can be usefull

SELECT priority, day, hour, stragg(name)
FROM table_name
GROUP BY priority, day, hour

and if it is not sufficient use LISTAGG

SELECT priority, day, hour, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name)
FROM table_name
GROUP BY priority, day, hour

edited Sep 07 '17 at 06:40

answered Sep 07 '17 at 06:26

Radim Bača

10,646
1
19
33

Stragg is not a standard oracle function. See this question:https://stackoverflow.com/questions/37340183/stragg-in-oracle-database-11g-enterprise-edition-release-11-2-0-4-0-64bit-prod – Rene Sep 07 '17 at 06:33
1

LISTAGG seem to work ! However I get the error :( ORA-01489: result of string concatenation is too long I suppose it's because I have oracle 11.2 ? Is there any way to avoid this error ? Thx – amans san Sep 07 '17 at 06:44
Ok, I am confused. You specify that you want to create a script in Perl, Python, Bash etc and also tag it as such, then someone gives a sql query, which I thought you knew, and that seems to be what you wanted? I will edit your question accordingly then. – Gerhard Sep 07 '17 at 06:52
@GerhardBarnard - I'm looking for one way or another ^^ – amans san Sep 07 '17 at 06:59
@amanssan So do you not know how to do the sql query or need help to write a script running the query? – Gerhard Sep 07 '17 at 07:05
@GerhardBarnard - It's the first time I'm using LISTAGG in a request. So I'm learning how to use it. The result is OK with it but now I need to figure out how to avoid the limitation which get me the error ORA-01489. I'm reading the link provided above to find the solution :) – amans san Sep 07 '17 at 07:10
Then have a look here. https://stackoverflow.com/questions/29776035/oracle-ora-01489-result-of-string-concatenation-is-too-long – Gerhard Sep 07 '17 at 07:15
Solved with XMLAGG ! Thx for your help ! – amans san Sep 07 '17 at 08:04
@simbabque He cannot list the answer as correct as this did not solve it, the link I sent to him in the comments on using XMLAGG did. – Gerhard Sep 07 '17 at 08:30
@GerhardBarnard then add that as an answer. ;-) – simbabque Sep 07 '17 at 08:31
1

@simbabque True. Will see if he comes back and vote for it then. Thanks – Gerhard Sep 07 '17 at 08:32
I added the vote but as @GerhardBarnard said, it was his answer which finally solved it. – amans san Sep 07 '17 at 12:18
@amanssan I will amend my answer and then you can select it as the correct one if you want. – Gerhard Sep 07 '17 at 12:26

Aggregate result from Oracle request

2 Answers2