Re: two records per row from query

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