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

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