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

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