Re: [SQL] two records per row from query
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 | Friday02-JAN-2009 | 2101
Thursday 08-JAN-2009 | 2101 | Friday09-JAN-2009 | 2101
Thursday 15-JAN-2009 | 2101 | |
Thursday 01-JAN-2009 | 2102 | Friday02-JAN-2009 | 2102
Saturday 03-JAN-2009 | 2102 | Thursday 08-JAN-2009 | 2102
Thursday 01-JAN-2009 | 2103 | Friday02-JAN-2009 | 2103
Saturday 03-JAN-2009 | 2103 | |
Sunday01-MAR-2009 | 2104 | Monday02-MAR-2009 | 2104
Tuesday 03-MAR-2009 | 2104 | Sunday08-MAR-2009 | 2104
Monday09-MAR-2009 | 2104 | Tuesday 10-MAR-2009 | 2104
Sunday15-MAR-2009 | 2104 | Monday16-MAR-2009 | 2104
Wednesday 01-APR-2009 | 2105 | Thursday 02-APR-2009 | 2105
Friday03-APR-2009 | 2105 | Wednesday 08-APR-2009 | 2105
Thursday 09-APR-2009 | 2105 | Friday10-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
Re: [SQL] 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 | Friday02-JAN-2009 | 2101
> Thursday 08-JAN-2009 | 2101 | Friday09-JAN-2009 | 2101
> Thursday 15-JAN-2009 | 2101 | |
> Thursday 01-JAN-2009 | 2102 | Friday02-JAN-2009 | 2102
> Saturday 03-JAN-2009 | 2102 | Thursday 08-JAN-2009 | 2102
> Thursday 01-JAN-2009 | 2103 | Friday02-JAN-2009 | 2103
> Saturday 03-JAN-2009 | 2103 | |
> Sunday01-MAR-2009 | 2104 | Monday02-MAR-2009 | 2104
> Tuesday 03-MAR-2009 | 2104 | Sunday08-MAR-2009 | 2104
> Monday09-MAR-2009 | 2104 | Tuesday 10-MAR-2009 | 2104
> Sunday15-MAR-2009 | 2104 | Monday16-MAR-2009 | 2104
> Wednesday 01-APR-2009 | 2105 | Thursday 02-APR-2009 | 2105
> Friday03-APR-2009 | 2105 | Wednesday 08-APR-2009 | 2105
> Thursday 09-APR-2009 | 2105 | Friday10-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_
