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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
