Thread: two records per row from query
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
In response to John : > 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 will try, but i'm not sure if i understand you correctly. Your table contains only 2 rows and both rows contains the same sessionid. Can i use that sessionid to find the rows that belongs together? Okay, my table: test=*# select * from mytable ;pkid | class_date | sessionid ------+------------+----------- 1 | 2009-01-01 | 2101 2 | 2009-01-02 | 2101 3 | 2009-02-01 | 2102 4 | 2009-02-02 | 2102 5 | 2009-03-01 | 2103 6 | 2009-03-02 | 2103 (6 rows) As you can see, there are 3 different sessionid's. test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1, classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as pkid, least(a.class_date, b.class_date) as classdate1, greatest(a.class_date, b.class_date) as classdate2, a.sessionid as sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable b on (a.sessionid=b.sessionid)) foo order by sessionid1, sessionid2,pkid;pkid | classdate1 | classdate2 | sessionid1 | sessionid2 ------+------------+------------+------------+------------ 1 | 2009-01-01 | 2009-01-01 | 2101 | 2101 3 |2009-02-01 | 2009-02-01 | 2102 | 2102 5 | 2009-03-01 | 2009-03-01 | 2103 | 2103 (3 rows) Hope that helps... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Wednesday 05 August 2009 10:21:08 pm A. Kretschmer wrote: > In response to John : > > 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 will try, but i'm not sure if i understand you correctly. Your table > contains only 2 rows and both rows contains the same sessionid. Can i > use that sessionid to find the rows that belongs together? > > Okay, my table: > > test=*# select * from mytable ; > pkid | class_date | sessionid > ------+------------+----------- > 1 | 2009-01-01 | 2101 > 2 | 2009-01-02 | 2101 > 3 | 2009-02-01 | 2102 > 4 | 2009-02-02 | 2102 > 5 | 2009-03-01 | 2103 > 6 | 2009-03-02 | 2103 > (6 rows) > > > As you can see, there are 3 different sessionid's. > > test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1, > classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as > pkid, least(a.class_date, b.class_date) as classdate1, > greatest(a.class_date, b.class_date) as classdate2, a.sessionid as > sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable > b on (a.sessionid=b.sessionid)) foo order by sessionid1, > sessionid2,pkid; > pkid | classdate1 | classdate2 | sessionid1 | sessionid2 > ------+------------+------------+------------+------------ > 1 | 2009-01-01 | 2009-01-01 | 2101 | 2101 > 3 | 2009-02-01 | 2009-02-01 | 2102 | 2102 > 5 | 2009-03-01 | 2009-03-01 | 2103 | 2103 > (3 rows) > > > Hope that helps... > > > Andreas Thanks - the sessionid's in fact do match. It's just that I can have more than two (2) classes per sessionid. So mytable might look like:select * from mytable1 2009/01/01 21012 2009/01/02 210132009/02/05 21014 2009/02/15 21015 2009/02/25 2101 I will try to use your solution. I was also looking at using an array aggregate. I'm not sure how I use it but it might work. Also I'm using 8.3.7 if that helps. Johnf
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-0200: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
In response to John : > Thanks - the sessionid's in fact do match. It's just that I can have more > than two (2) classes per sessionid. So mytable might look like: > select * from mytable > 1 2009/01/01 2101 > 2 2009/01/02 2101 > 3 2009/02/05 2101 > 4 2009/02/15 2101 > 5 2009/02/25 2101 Can you show/explain, which rows in your example contains now the values for the new row? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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 )
John wrote:
[snip]
>
> 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.
>
>
[snip]
I hope I understand now.
I can not give you a pure SQL solution, where you only have a single
select. For this, I'm missing things like analytic-functions and
subquery-factoring in PostgreSQL. I'm coming from Oracle where it would
be easier for me.
Nevertheless, I'll give you here my way to get the result.
I have:
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-0200:00:00 | 2103 7 | 2009-01-03 00:00:00 | 2103 8 | 2009-01-08 00:00:00 | 2101 9 |
2009-01-0900:00:00 | 2101 10 | 2009-01-15 00:00:00 | 2101 11 | 2009-01-03 00:00:00 | 2102 12 |
2009-01-0800:00:00 | 2102 13 | 2009-03-01 00:00:00 | 2104 14 | 2009-03-02 00:00:00 | 2104 15 |
2009-03-0300:00:00 | 2104 16 | 2009-03-08 00:00:00 | 2104 17 | 2009-03-09 00:00:00 | 2104 18 |
2009-03-1000:00:00 | 2104 19 | 2009-03-15 00:00:00 | 2104 20 | 2009-03-16 00:00:00 | 2104 21 |
2009-04-0100:00:00 | 2105 22 | 2009-04-02 00:00:00 | 2105 23 | 2009-04-03 00:00:00 | 2105 24 |
2009-04-0800:00:00 | 2105 25 | 2009-04-09 00:00:00 | 2105 26 | 2009-04-10 00:00:00 | 2105 27 |
2009-04-1500:00:00 | 2105
(27 rows)
lem=#
and this is what I get:
lem=# \i q1.sql
BEGIN
CREATE SEQUENCE
CREATE SEQUENCE
SELECT class_date1 | sessionid1 | class_date2 | sessionid2
-----------------------+------------+-----------------------+------------Thursday 01-JAN-2009 | 2101 | Friday
02-JAN-2009| 2101Thursday 08-JAN-2009 | 2101 | Friday 09-JAN-2009 | 2101Thursday 15-JAN-2009 |
2101 | |Thursday 01-JAN-2009 | 2102 | Friday 02-JAN-2009 | 2102Saturday
03-JAN-2009| 2102 | Thursday 08-JAN-2009 | 2102Thursday 01-JAN-2009 | 2103 | Friday 02-JAN-2009
| 2103Saturday 03-JAN-2009 | 2103 | |Sunday 01-MAR-2009 | 2104 | Monday
02-MAR-2009| 2104Tuesday 03-MAR-2009 | 2104 | Sunday 08-MAR-2009 | 2104Monday 09-MAR-2009 |
2104 | Tuesday 10-MAR-2009 | 2104Sunday 15-MAR-2009 | 2104 | Monday 16-MAR-2009 |
2104Wednesday01-APR-2009 | 2105 | Thursday 02-APR-2009 | 2105Friday 03-APR-2009 | 2105 |
Wednesday08-APR-2009 | 2105Thursday 09-APR-2009 | 2105 | Friday 10-APR-2009 | 2105Wednesday
15-APR-2009| 2105 | |
(15 rows)
ROLLBACK
lem=#
my q1.sql-file looks like this, though you can play around:
begin;
create sequence mytable_seq;
create sequence myreport_seq;
create temp table myreport on commit drop as
select nextval('myreport_seq') as myrn ,t2.mycolcount ,t2.pkid ,t2.class_date ,t2.sessionid
from ( select mod(nextval('mytable_seq'), 2) as mycolcount ,t1.pkid ,t1.class_date
,t1.sessionid from ( select v3.pkid ,v3.class_date
,v3.sessionid from ( select pkid ,class_date
,sessionid from mytable union all
selectnull ,null ,v2.sessionid
from ( select sessionid from ( select sessionid
,mod(count(*), 2) as
extra_row from mytable group by
sessionid ) v1 where v1.extra_row = 1
) v2 ) v3 order by v3.sessionid, v3.class_date nulls last
) t1 ) t2
;
select r1.class_date as class_date1 ,r1.sessionid as sessionid1 ,r2.class_date as class_date2 ,case when
r2.class_dateis null then null else r2.sessionid end
as sessionid2
from myreport r1 ,myreport r2
where r1.sessionid = r2.sessionid
and r1.myrn = r2.myrn - 1
and r1.mycolcount = 1
order by r1.sessionid, r1.class_date
;
rollback;
Hope this helps or somebody else has a more elegant solution
Cheers, Leo
On Friday 07 August 2009 02:50:48 am Leo Mannhart wrote:
> John wrote:
> [snip]
>
> > 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.
>
> [snip]
>
> I hope I understand now.
> I can not give you a pure SQL solution, where you only have a single
> select. For this, I'm missing things like analytic-functions and
> subquery-factoring in PostgreSQL. I'm coming from Oracle where it would
> be easier for me.
> Nevertheless, I'll give you here my way to get the result.
>
> I have:
>
> 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
> 8 | 2009-01-08 00:00:00 | 2101
> 9 | 2009-01-09 00:00:00 | 2101
> 10 | 2009-01-15 00:00:00 | 2101
> 11 | 2009-01-03 00:00:00 | 2102
> 12 | 2009-01-08 00:00:00 | 2102
> 13 | 2009-03-01 00:00:00 | 2104
> 14 | 2009-03-02 00:00:00 | 2104
> 15 | 2009-03-03 00:00:00 | 2104
> 16 | 2009-03-08 00:00:00 | 2104
> 17 | 2009-03-09 00:00:00 | 2104
> 18 | 2009-03-10 00:00:00 | 2104
> 19 | 2009-03-15 00:00:00 | 2104
> 20 | 2009-03-16 00:00:00 | 2104
> 21 | 2009-04-01 00:00:00 | 2105
> 22 | 2009-04-02 00:00:00 | 2105
> 23 | 2009-04-03 00:00:00 | 2105
> 24 | 2009-04-08 00:00:00 | 2105
> 25 | 2009-04-09 00:00:00 | 2105
> 26 | 2009-04-10 00:00:00 | 2105
> 27 | 2009-04-15 00:00:00 | 2105
> (27 rows)
>
> lem=#
>
> and this is what I get:
>
> lem=# \i q1.sql
> BEGIN
> CREATE SEQUENCE
> CREATE SEQUENCE
> SELECT
> class_date1 | sessionid1 | class_date2 | sessionid2
> -----------------------+------------+-----------------------+------------
> Thursday 01-JAN-2009 | 2101 | Friday 02-JAN-2009 | 2101
> Thursday 08-JAN-2009 | 2101 | Friday 09-JAN-2009 | 2101
> Thursday 15-JAN-2009 | 2101 | |
> Thursday 01-JAN-2009 | 2102 | Friday 02-JAN-2009 | 2102
> Saturday 03-JAN-2009 | 2102 | Thursday 08-JAN-2009 | 2102
> Thursday 01-JAN-2009 | 2103 | Friday 02-JAN-2009 | 2103
> Saturday 03-JAN-2009 | 2103 | |
> Sunday 01-MAR-2009 | 2104 | Monday 02-MAR-2009 | 2104
> Tuesday 03-MAR-2009 | 2104 | Sunday 08-MAR-2009 | 2104
> Monday 09-MAR-2009 | 2104 | Tuesday 10-MAR-2009 | 2104
> Sunday 15-MAR-2009 | 2104 | Monday 16-MAR-2009 | 2104
> Wednesday 01-APR-2009 | 2105 | Thursday 02-APR-2009 | 2105
> Friday 03-APR-2009 | 2105 | Wednesday 08-APR-2009 | 2105
> Thursday 09-APR-2009 | 2105 | Friday 10-APR-2009 | 2105
> Wednesday 15-APR-2009 | 2105 | |
> (15 rows)
>
> ROLLBACK
> lem=#
>
> my q1.sql-file looks like this, though you can play around:
>
> begin;
> create sequence mytable_seq;
> create sequence myreport_seq;
> create temp table myreport on commit drop as
> select nextval('myreport_seq') as myrn
> ,t2.mycolcount
> ,t2.pkid
> ,t2.class_date
> ,t2.sessionid
> from ( select mod(nextval('mytable_seq'), 2) as mycolcount
> ,t1.pkid
> ,t1.class_date
> ,t1.sessionid
> from ( select v3.pkid
> ,v3.class_date
> ,v3.sessionid
> from ( select pkid
> ,class_date
> ,sessionid
> from mytable
> union all
> select null
> ,null
> ,v2.sessionid
> from ( select sessionid
> from ( select sessionid
> ,mod(count(*), 2) as
> extra_row
> from mytable
> group by sessionid
> ) v1
> where v1.extra_row = 1
> ) v2
> ) v3
> order by v3.sessionid, v3.class_date nulls last
> ) t1
> ) t2
> ;
> select r1.class_date as class_date1
> ,r1.sessionid as sessionid1
> ,r2.class_date as class_date2
> ,case when r2.class_date is null then null else r2.sessionid end
> as sessionid2
> from myreport r1
> ,myreport r2
> where r1.sessionid = r2.sessionid
> and r1.myrn = r2.myrn - 1
> and r1.mycolcount = 1
> order by r1.sessionid, r1.class_date
> ;
> rollback;
>
> Hope this helps or somebody else has a more elegant solution
>
> Cheers, Leo
Wow thanks !
Johnf