Re: two records per row from query

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),
availseats numeric(5),
depart integer,
stop_close boolean DEFAULT false,
langid integer,
monday boolean DEFAULT false,
tuesday boolean 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 character varying(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 character varying(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,
locationid integer,
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_class integer 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),
referred date,
reminded date,
warned date,
earliest date,
latest date,
enrolled date,
holdtill date,
musent date,
mucallrcd date,
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,
confirmltr date,
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_cost numeric(10,2) DEFAULT 0.00,
community_service integer DEFAULT 0,
cumminity_tobedeter boolean DEFAULT true,
restitution_tobedeter boolean DEFAULT true,
note text,
course_note text,
restitution_note text,
community_note text,
previous_enroll integer 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 text DEFAULT ''::text,
comp_note text DEFAULT ''::text,
enroll_note text DEFAULT ''::text,
course_pay_sched text,
CONSTRAINT esenroll_pkey PRIMARY KEY (pkid),
CONSTRAINT fk_esclient1 FOREIGN KEY (fk_clientid)
REFERENCES esclient (pkid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)