Re: two records per row from query - Mailing list pgsql-sql

On Thursday 06 August 2009 06:42:34 am Leo Mannhart wrote:
> John wrote:
> > mytable
> > pkid
> > class_date.
> > sessionid
> >
> > select * from mytable
> > 1 2009/01/01 2101
> > 2 2009/01/02 2101
> >
> > I would like an SQL that would produce
> >
> > newtable
> > pkid,
> > class_date1,
> > class_date2,
> > sessionid1,
> > sessionid2
> >
> > Select * from newtable
> >
> > 1 2009/01/01 2009/01/02 2101 2101
> >
> > I have a list of classes that is perfect for our needs.  However, I need
> > to create the second table (from a query) to feed to a report writer so
> > it can write out a single line of text for two records.
> > Like:
> >
> > Your class dates are as follows
> >
> >    Date              Date
> > 01/01/2009           01/02/2009
> > 01/08/2009           01/10/2009
> > 03/31/2009           04/05/2009
> > and will continue until the all the classes are printed.
> >
> > The problem of course is the table has a row per class and the report
> > writer needs two class dates per row.
> >
> > I have no idea how to do this using SQL.
> >
> > Thanks in advance,
> > Johnf
>
> Can you give a more precise example please? I don't get what you really
> need. What I understand is that you want 1 record back for each
> sessionid with the earliest and latest class_date.
>
> I've done the following:
>
> lem=# select * from mytable;
>  pkid |     class_date      | sessionid
> ------+---------------------+-----------
>     1 | 2009-01-01 00:00:00 |      2101
>     2 | 2009-01-02 00:00:00 |      2101
>     3 | 2009-01-01 00:00:00 |      2102
>     4 | 2009-01-02 00:00:00 |      2102
>     5 | 2009-01-01 00:00:00 |      2103
>     6 | 2009-01-02 00:00:00 |      2103
>     7 | 2009-01-03 00:00:00 |      2103
> (7 rows)
>
> and then:
>
> lem=# select min(pkid) as pkid
> lem-#       ,min(class_date) as class_date1
> lem-#       ,max(class_date) as class_date2
> lem-#       ,sessionid
> lem-# from   mytable
> lem-# group by sessionid;
>  pkid |     class_date1     |     class_date2     | sessionid
> ------+---------------------+---------------------+-----------
>     5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 |      2103
>     3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 |      2102
>     1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 |      2101
> (3 rows)
>
> Is this what you need or is there something else? Can you give more
> sample data and the result you expect from it?
>
>
> Cheers, Leo

I'm sorry I was attempting to simplify the problem.  I will attempt to provide 
more info:

OVERVIEW:
"mytable" contains the dates of the classes a student will attend along with 
fields to identify the student (not really it's normalized).  One row per 
class. In general the student signs up for a session.  A session has many 
classes that run for some length of time.  Normally, a few months.  Classes 
maybe on some set schedule or not.  Maybe on each Saturday and Sunday for two 
months - maybe a total of 16 classes.

What I need is a way to gather the classes two (maybe three) at a time into 
one row.  I need this because the report writer processes the data one row at 
a time.  And I need the report writer to print two class dates on one line of 
the report.

So the output would look similar to the follows on the report:

Your class schedule is as follows:

Saturday   01/03/2009           Sunday 01/04/2009
Saturday   01/10/2009           Sunday 01/11/2009
Saturday   01/17/2009           Sunday 01/18/2009

And of course the schedule will continue until all the classes are print.  
Also note that the dates are in order from left to right and then down.


THE PROBLEM:

Since the classes are in a single row per class I need a way to get two 
classes into a single row to allow the report writer to print two classes per 
row.  I don't know how too!


In general the sessionid will be the same but it is not the only thing I'm 
using to find the student.

The "essess" table is the available sessions.
The "esclass" contains the classes and any reschedule classes with a FK into 
essess
The 'esenroll' has the student, the session.

This is converted from an old Visual Fox Pro program.

The actual tables in question

The sessions:
CREATE TABLE essess
( pkid serial NOT NULL, sessionid_do_not_use integer, courseid integer, instrid integer, sequenceid integer, began
date,ended date, cancelled boolean, name_1 character varying(35), locationid integer, facility character varying(35),
availseatsnumeric(5), depart integer, stop_close boolean DEFAULT false, langid integer, monday boolean DEFAULT false,
tuesdayboolean DEFAULT false, wedesday boolean DEFAULT false, thursday boolean DEFAULT false, friday boolean DEFAULT
false,saturday boolean DEFAULT false, sunday boolean DEFAULT false, end_time character varying(10), start_time
charactervarying(10), note character varying, eligiblecourses text, topic integer, total_hours numeric(5,1) DEFAULT
0.0,total_classes integer DEFAULT 0, CONSTRAINT essess_pkey PRIMARY KEY (pkid)
 
)


The class table
CREATE TABLE esclass
( pkid serial NOT NULL, classid_do_not_use integer, courseid integer, languageid integer, zoneid integer, name_1
charactervarying(30), schedule date, weekday character varying(10), sessionid integer, starthr numeric(2), startmin
numeric(2),am_or_pm numeric(1), instrid integer, facility character varying(35), classseq numeric(5), depart integer,
locationidinteger, starttime character varying(10) DEFAULT '9:00AM'::character varying, endtime character varying(10)
DEFAULT'3:30PM'::character varying, isholiday boolean DEFAULT false, atten_taken boolean DEFAULT false,
fk_rescheduled_classinteger DEFAULT 0, -- if the class is rescheduled the 
 
new class pkid is stored here CONSTRAINT esclass_pkey PRIMARY KEY (pkid)
)

The enrollment:

CREATE TABLE esenroll
( pkid serial NOT NULL, enrollid_do_not_use integer, agencyid integer, clientid integer, caseno character varying(13),
referreddate, reminded date, warned date, earliest date, latest date, enrolled date, holdtill date, musent date,
mucallrcddate, scheduled date, deadline date, completed date, failed date, notify_1 boolean, newfld character
varying(10),sessionid integer, onhold boolean, not_sched boolean, depart integer, enroll_dte date, final_1 date,
confirmltrdate, agnoteltr date, schedltr date, makeupltr date, hd_all_mai boolean, fk_clientid integer, fk_escourts
integer,fk_essess integer, fk_escourse integer, fk_topicid integer, fk_pccode integer DEFAULT 0, ref_no character
varying(60),mustenrollby date, req_hrs integer DEFAULT 0, refer_again boolean DEFAULT false, ret_to_court date DEFAULT
now(),rereferred date, yntbd character(1) DEFAULT ''::bpchar, restitution numeric(10,2) DEFAULT 0, terminated date,
course_costnumeric(10,2) DEFAULT 0.00, community_service integer DEFAULT 0, cumminity_tobedeter boolean DEFAULT true,
restitution_tobedeterboolean DEFAULT true, note text, course_note text, restitution_note text, community_note text,
previous_enrollinteger DEFAULT 0, inactive date, locked boolean DEFAULT false, refer_atten_hrs numeric(10,1) DEFAULT
0.0,hold_until date, moved_to integer DEFAULT 0, fk_esagcontacts integer, mandatory boolean DEFAULT false, term_note
textDEFAULT ''::text, comp_note text DEFAULT ''::text, enroll_note text DEFAULT ''::text, course_pay_sched text,
CONSTRAINTesenroll_pkey PRIMARY KEY (pkid), CONSTRAINT fk_esclient1 FOREIGN KEY (fk_clientid)     REFERENCES esclient
(pkid)MATCH SIMPLE     ON UPDATE NO ACTION ON DELETE NO ACTION
 
)