CODEWITHSUNDEEP
sqloracle-database
samman adhikari
samman adhikari

Reputation: 651

How can I group date from multiple rows into one in Oracle?

There are two tables 'event' and 'club'. Since they have many to many relationship there is also a junction table 'club_event'. I created a view which holds all the columns in event table with the club_name from the club table that organized the event. The problem is that when a event has two or more organizing clubs there will be multiple copies of a entire row for that event in the view table. Rather than this I want different club_name to be kept as a comma separated values in columns. How may I do this?

CREATE OR REPLACE FORCE VIEW "V_EVENT" ("EVENT_ID", "EVENT_NAME", "START_DATE", "END_DATE", "START_TIME", "END_TIME", "DESCRIPTION", "FROM_COLLEGE", "FROM_COMMUNITY", "CLUB_NAME") AS 
  SELECT event."EVENT_ID",event."EVENT_NAME",event."START_DATE",event."END_DATE",event."START_TIME",event."END_TIME",event."DESCRIPTION",event."FROM_COLLEGE",event."FROM_COMMUNITY", club_name
FROM event
LEFT JOIN (club_event INNER JOIN club ON club_event.club_id = club.club_id)
ON event.event_id = club_event.event_id
/

Upvotes: 0

Views: 84

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

A correlated subquery is not only simpler to write, but probably also has better performance because it avoids the outer aggregation:

SELECT . . .   -- the columns you want from event,
      (SELECT LISTAGG(c.club_name, ',') WITHIN GROUP (ORDER BY c.club_name)
       FROM club_event ce INNER JOIN
            club c
            ON ce.club_id = c.club_id
       WHERE e.event_id = ce.event_id
      ) clubs
FROM event e;

Upvotes: 0

Gabriel Durac
Gabriel Durac

Reputation: 2770

Please mention your Oracle version. In Oracle 11g Release 2 LISTAGG was introduced making such a task very simple:

SELECT event."EVENT_ID"
      ,event."EVENT_NAME"
      ,event."START_DATE"
      ,event."END_DATE"
      ,event."START_TIME"
      ,event."END_TIME"
      ,event."DESCRIPTION"
      ,event."FROM_COLLEGE"
      ,event."FROM_COMMUNITY"
      ,LISTAGG(club_name, ',') WITHIN GROUP ( ORDER BY club_name) AS clubs
   FROM event
   LEFT JOIN (club_event INNER JOIN club ON club_event.club_id = club.club_id)
   ON event.event_id = club_event.event_id
   GROUP BY event."EVENT_ID"
      ,event."EVENT_NAME"
      ,event."START_DATE"
      ,event."END_DATE"
      ,event."START_TIME"
      ,event."END_TIME"
      ,event."DESCRIPTION"
      ,event."FROM_COLLEGE"
      ,event."FROM_COMMUNITY"

If you are running on a version prior you can try rolling out your own user defined aggregate function as described here

The GROUP BY Statement in SQL is used for aggregations. Since LISTAGG is an aggregate function the database needs to know which columns need to be grouped. In your case, it was all the columns you were selecting and which did not have an aggregation function applied. The documentation of aggregate functions probably does a better job at explaining than I can.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143093

Suppose that query you wrote returns desired result.

LISTAGG function is nowaday used to do what you want - put all club names into a single column, separated by some separator (doesn't have to be a comma). As it aggregates result, you'll have to put all other non-aggregated columns into the group by clause.

Something like this:

create or replace force view v_event as 
  select e.event_id,
    e.event_name,
    e.start_date,
    e.end_date,
    e.start_time,
    e.end_time,
    e.description,
    e.from_college,
    e.from_community, 
    listagg(c.club_name, ',') within group (order by c.club_name) club_name
from event e
left join (club_event ce inner join club c on ce.club_id = c.club_id) 
  on e.event_id = ce.event_id
group by  
    e.event_id,
    e.event_name,
    e.start_date,
    e.end_date,
    e.start_time,
    e.end_time,
    e.description,
    e.from_college,
    e.from_community
/

Also, as we're using Oracle, get rid of double quotes; they cause nothing but problems.

Upvotes: 1

Related Questions