analytics query - oracle sql

I’m writing a report around students taxi routes (taxis to and from school) and need some assistance.

Please see the attachment which some sample data.

fig 1. output from routes table
this lists the route details for each student. Each students route could be:
  * an outbound trip only - a lift from home to school (2 Map ID numbers - MAP_ID1 and MAP_ID2)
  * an outbound and inbound trip - a lift from home to school, and from school to home (4 map ID numbers - MAP_ID1, MAP_ID2, MAP_ID3, MAP_ID4)
  * an inbound trip only - a lift from school to home (2 Map ID numbers - MAP_ID3 and MAP_ID4)
The MAP_ID* fields indicate the students route start and route end points.


fig 2. output from stops table
this lists all the stops within each route (and the route start and end points). Each route may have multiple stops, e.g.
   *  route_id 37682 - this route transports one student to and from school
   *  route_id 37685 - this route transports one student from home to school only
   *  route_id 37687 - this route transports multiple students from their homes to school, and from school back home
The stop_type column indicates if the stop is at an address (A) or school (B).

fig 3. the desired output
I need the report to list all the stops for each student (and the route start and end point) in columns Outbound and Inbound. The stops need to be concatenated into either column. For example:
   *  student 208865 (route 37687) - has 3 stops in Outbound -'Student Five (Home Address), Student Six (Home Address), Another School', and 3 stops in inbound - 'Another School, Student Six (Home Address), Student Five (Home Address)'

Any help is appreciated.
ee-output.xlsx
tonMachine100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mankowitzCommented:
Assuming that map_id is always ordinal, you want something like this:

select r.route_id, r.stud_id, r.stud_name,
r.map_id1, r.map_id2, r.map_id3, r.map_id4,

(select group_concat(descrp) from stops where route_id=r.route_id
and map_id between r.map_id1 and r.map_id2) as Outbound,

(select group_concat(descrp) from stops where route_id=r.route_id
and map_id between r.map_id3 and r.map_id4) as Inbound

from routes r join stops s on (r.route_id=s.route_id)
group by r.stud_id
0
mankowitzCommented:
0
sdstuberCommented:
try this...  

similar idea to above except I removed extraneous join to stops, removed illegal group by and changed group_concat which isn't an oracle function (or at least not one built in ) to the listagg function which comes with 11gR2 and above


  SELECT r.*,
         (SELECT LISTAGG(descrp, ',') WITHIN GROUP (ORDER BY step)
            FROM stops s
           WHERE s.route_id = r.route_id AND s.map_id between r.map_id1 and r.map_id2)
             outbound,
         (SELECT LISTAGG(descrp, ',') WITHIN GROUP (ORDER BY step)
            FROM stops s
           WHERE s.route_id = r.route_id AND s.map_id between r.map_id3 and map_id4)
             inbound
    FROM routes r
ORDER BY route_id, stud_id

I tested this using your sample data and it produced the expected results
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mankowitzCommented:
D'oh,
beat me to it. I realized that it was oracle, not mysql.

SELECT r.route_id, 
       r.stud_id,
       r.stud_name,
       r.map_id1,
       r.map_id2,
       r.map_id3,
       r.map_id4,
       (SELECT Listagg(descrp) 
                 within GROUP (ORDER BY map_id) 
        FROM   stops  
        WHERE  route_id = r.route_id 
               AND map_id BETWEEN r.map_id1 AND r.map_id2 
        ) AS Outbound, 
       (SELECT Listagg(descrp) 
                 within GROUP (ORDER BY map_id) 
        FROM   stops 
        WHERE  route_id = r.route_id 
               AND map_id BETWEEN r.map_id3 AND r.map_id4 
        ) AS Inbound 
FROM   routes r   

Open in new window

0
sdstuberCommented:
that query still has an extraneous join  and illegal grouping - it doesn't execute
0
mankowitzCommented:
That's odd. It works for me. See http://sqlfiddle.com/#!4/12103f/12
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
you changed it

I was commenting on your original version which looked like this:
(tonMachine100 - don't use this, it's a copy of a previous incorrect query)
  SELECT MAX(r.route_id),
         MAX(r.stud_id),
         MAX(r.stud_name),
         MAX(r.map_id1),
         MAX(r.map_id2),
         MAX(r.map_id3),
         MAX(r.map_id4),
         (SELECT LISTAGG(descrp) WITHIN GROUP (ORDER BY map_id)
            FROM stops
           WHERE route_id = r.route_id AND map_id BETWEEN r.map_id1 AND r.map_id2)
             AS outbound,
         (SELECT LISTAGG(descrp) WITHIN GROUP (ORDER BY map_id)
            FROM stops
           WHERE route_id = r.route_id AND map_id BETWEEN r.map_id3 AND r.map_id4)
             AS inbound
    FROM routes r JOIN stops s ON (r.route_id = s.route_id)
GROUP BY r.stud_id


your current version is the same as mine now, except you changed the ordering column, but either step or map_id works for the given sample data
0
mankowitzCommented:
yes, I posted it and then realized that I had copy/pasted the wrong version, so I updated it.
0
tonMachine100Author Commented:
this is spot on - thanks chaps
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.