Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 999E2632946 for ; Fri, 7 Aug 2009 06:51:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 46998-01 for ; Fri, 7 Aug 2009 09:50:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ews43.everyware.ch (ews43.everyware.ch [212.71.111.43]) by mail.postgresql.org (Postfix) with ESMTP id 7A1E3632517 for ; Fri, 7 Aug 2009 06:50:53 -0300 (ADT) Received: from mars.lemaco.ch [212.71.105.68] by ews43.everyware.ch with ESMTP (SMTPD32-8.15) id A8F8BF090062; Fri, 07 Aug 2009 11:50:48 +0200 Message-ID: <4A7BF8F8.3090907@beecom.ch> Date: Fri, 07 Aug 2009 11:50:48 +0200 From: Leo Mannhart User-Agent: Thunderbird 2.0.0.22 (X11/20090608) MIME-Version: 1.0 To: John CC: pgsql-sql@postgresql.org Subject: Re: two records per row from query References: <200908051651.13235.jfabiani@yolo.com> <4A7ADDCA.2060809@beecom.ch> <200908060846.40955.jfabiani@yolo.com> In-Reply-To: <200908060846.40955.jfabiani@yolo.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.3 tagged_above=-10 required=5 tests=AWL=1.300, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/33 X-Sequence-Number: 32942 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