pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtwo records per row from query
8+ messages / 3 participants
[nested] [flat]
* two records per row from query
@ 2009-08-05 23:51 John <jfabiani@yolo.com>
2009-08-06 05:21 ` Re: two records per row from query A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-06 13:42 ` Re: two records per row from query Leo Mannhart <leo.mannhart@beecom.ch>
0 siblings, 2 replies; 8+ messages in thread
From: John @ 2009-08-05 23:51 UTC (permalink / raw)
To: pgsql-sql
mytable
pkid
class_date.
sessionid
select * from mytable
1 2009/01/01 2101
2 2009/01/02 2101
I would like an SQL that would produce
newtable
pkid,
class_date1,
class_date2,
sessionid1,
sessionid2
Select * from newtable
1 2009/01/01 2009/01/02 2101 2101
I have a list of classes that is perfect for our needs. However, I need to
create the second table (from a query) to feed to a report writer so it can
write out a single line of text for two records.
Like:
Your class dates are as follows
Date Date
01/01/2009 01/02/2009
01/08/2009 01/10/2009
03/31/2009 04/05/2009
and will continue until the all the classes are printed.
The problem of course is the table has a row per class and the report writer
needs two class dates per row.
I have no idea how to do this using SQL.
Thanks in advance,
Johnf
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
@ 2009-08-06 05:21 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-06 13:48 ` Re: two records per row from query John <jfabiani@yolo.com>
1 sibling, 1 reply; 8+ messages in thread
From: A. Kretschmer @ 2009-08-06 05:21 UTC (permalink / raw)
To: pgsql-sql
In response to John :
> mytable
> pkid
> class_date.
> sessionid
>
> select * from mytable
> 1 2009/01/01 2101
> 2 2009/01/02 2101
>
> I would like an SQL that would produce
>
> newtable
> pkid,
> class_date1,
> class_date2,
> sessionid1,
> sessionid2
>
> Select * from newtable
>
> 1 2009/01/01 2009/01/02 2101 2101
I will try, but i'm not sure if i understand you correctly. Your table
contains only 2 rows and both rows contains the same sessionid. Can i
use that sessionid to find the rows that belongs together?
Okay, my table:
test=*# select * from mytable ;
pkid | class_date | sessionid
------+------------+-----------
1 | 2009-01-01 | 2101
2 | 2009-01-02 | 2101
3 | 2009-02-01 | 2102
4 | 2009-02-02 | 2102
5 | 2009-03-01 | 2103
6 | 2009-03-02 | 2103
(6 rows)
As you can see, there are 3 different sessionid's.
test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1,
classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as
pkid, least(a.class_date, b.class_date) as classdate1,
greatest(a.class_date, b.class_date) as classdate2, a.sessionid as
sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable
b on (a.sessionid=b.sessionid)) foo order by sessionid1,
sessionid2,pkid;
pkid | classdate1 | classdate2 | sessionid1 | sessionid2
------+------------+------------+------------+------------
1 | 2009-01-01 | 2009-01-01 | 2101 | 2101
3 | 2009-02-01 | 2009-02-01 | 2102 | 2102
5 | 2009-03-01 | 2009-03-01 | 2103 | 2103
(3 rows)
Hope that helps...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
2009-08-06 05:21 ` Re: two records per row from query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2009-08-06 13:48 ` John <jfabiani@yolo.com>
2009-08-06 14:21 ` Re: two records per row from query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 8+ messages in thread
From: John @ 2009-08-06 13:48 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 05 August 2009 10:21:08 pm A. Kretschmer wrote:
> In response to John :
> > mytable
> > pkid
> > class_date.
> > sessionid
> >
> > select * from mytable
> > 1 2009/01/01 2101
> > 2 2009/01/02 2101
> >
> > I would like an SQL that would produce
> >
> > newtable
> > pkid,
> > class_date1,
> > class_date2,
> > sessionid1,
> > sessionid2
> >
> > Select * from newtable
> >
> > 1 2009/01/01 2009/01/02 2101 2101
>
> I will try, but i'm not sure if i understand you correctly. Your table
> contains only 2 rows and both rows contains the same sessionid. Can i
> use that sessionid to find the rows that belongs together?
>
> Okay, my table:
>
> test=*# select * from mytable ;
> pkid | class_date | sessionid
> ------+------------+-----------
> 1 | 2009-01-01 | 2101
> 2 | 2009-01-02 | 2101
> 3 | 2009-02-01 | 2102
> 4 | 2009-02-02 | 2102
> 5 | 2009-03-01 | 2103
> 6 | 2009-03-02 | 2103
> (6 rows)
>
>
> As you can see, there are 3 different sessionid's.
>
> test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1,
> classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as
> pkid, least(a.class_date, b.class_date) as classdate1,
> greatest(a.class_date, b.class_date) as classdate2, a.sessionid as
> sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable
> b on (a.sessionid=b.sessionid)) foo order by sessionid1,
> sessionid2,pkid;
> pkid | classdate1 | classdate2 | sessionid1 | sessionid2
> ------+------------+------------+------------+------------
> 1 | 2009-01-01 | 2009-01-01 | 2101 | 2101
> 3 | 2009-02-01 | 2009-02-01 | 2102 | 2102
> 5 | 2009-03-01 | 2009-03-01 | 2103 | 2103
> (3 rows)
>
>
> Hope that helps...
>
>
> Andreas
Thanks - the sessionid's in fact do match. It's just that I can have more
than two (2) classes per sessionid. So mytable might look like:
select * from mytable
1 2009/01/01 2101
2 2009/01/02 2101
3 2009/02/05 2101
4 2009/02/15 2101
5 2009/02/25 2101
I will try to use your solution.
I was also looking at using an array aggregate. I'm not sure how I use it but
it might work.
Also I'm using 8.3.7 if that helps.
Johnf
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
2009-08-06 05:21 ` Re: two records per row from query A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-06 13:48 ` Re: two records per row from query John <jfabiani@yolo.com>
@ 2009-08-06 14:21 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2009-08-06 14:21 UTC (permalink / raw)
To: pgsql-sql
In response to John :
> Thanks - the sessionid's in fact do match. It's just that I can have more
> than two (2) classes per sessionid. So mytable might look like:
> select * from mytable
> 1 2009/01/01 2101
> 2 2009/01/02 2101
> 3 2009/02/05 2101
> 4 2009/02/15 2101
> 5 2009/02/25 2101
Can you show/explain, which rows in your example contains now the values
for the new row?
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
@ 2009-08-06 13:42 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-08-06 15:46 ` Re: two records per row from query John <jfabiani@yolo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Leo Mannhart @ 2009-08-06 13:42 UTC (permalink / raw)
To: John <jfabiani@yolo.com>; +Cc: pgsql-sql
John wrote:
> mytable
> pkid
> class_date.
> sessionid
>
> select * from mytable
> 1 2009/01/01 2101
> 2 2009/01/02 2101
>
> I would like an SQL that would produce
>
> newtable
> pkid,
> class_date1,
> class_date2,
> sessionid1,
> sessionid2
>
> Select * from newtable
>
> 1 2009/01/01 2009/01/02 2101 2101
>
> I have a list of classes that is perfect for our needs. However, I need to
> create the second table (from a query) to feed to a report writer so it can
> write out a single line of text for two records.
> Like:
>
> Your class dates are as follows
>
> Date Date
> 01/01/2009 01/02/2009
> 01/08/2009 01/10/2009
> 03/31/2009 04/05/2009
> and will continue until the all the classes are printed.
>
> The problem of course is the table has a row per class and the report writer
> needs two class dates per row.
>
> I have no idea how to do this using SQL.
>
> Thanks in advance,
> Johnf
>
Can you give a more precise example please? I don't get what you really
need. What I understand is that you want 1 record back for each
sessionid with the earliest and latest class_date.
I've done the following:
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
(7 rows)
and then:
lem=# select min(pkid) as pkid
lem-# ,min(class_date) as class_date1
lem-# ,max(class_date) as class_date2
lem-# ,sessionid
lem-# from mytable
lem-# group by sessionid;
pkid | class_date1 | class_date2 | sessionid
------+---------------------+---------------------+-----------
5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 | 2103
3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2102
1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2101
(3 rows)
Is this what you need or is there something else? Can you give more
sample data and the result you expect from it?
Cheers, Leo
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
2009-08-06 13:42 ` Re: two records per row from query Leo Mannhart <leo.mannhart@beecom.ch>
@ 2009-08-06 15:46 ` John <jfabiani@yolo.com>
2009-08-07 09:50 ` Re: two records per row from query Leo Mannhart <leo.mannhart@beecom.ch>
0 siblings, 1 reply; 8+ messages in thread
From: John @ 2009-08-06 15:46 UTC (permalink / raw)
To: pgsql-sql
On Thursday 06 August 2009 06:42:34 am Leo Mannhart wrote:
> John wrote:
> > mytable
> > pkid
> > class_date.
> > sessionid
> >
> > select * from mytable
> > 1 2009/01/01 2101
> > 2 2009/01/02 2101
> >
> > I would like an SQL that would produce
> >
> > newtable
> > pkid,
> > class_date1,
> > class_date2,
> > sessionid1,
> > sessionid2
> >
> > Select * from newtable
> >
> > 1 2009/01/01 2009/01/02 2101 2101
> >
> > I have a list of classes that is perfect for our needs. However, I need
> > to create the second table (from a query) to feed to a report writer so
> > it can write out a single line of text for two records.
> > Like:
> >
> > Your class dates are as follows
> >
> > Date Date
> > 01/01/2009 01/02/2009
> > 01/08/2009 01/10/2009
> > 03/31/2009 04/05/2009
> > and will continue until the all the classes are printed.
> >
> > The problem of course is the table has a row per class and the report
> > writer needs two class dates per row.
> >
> > I have no idea how to do this using SQL.
> >
> > Thanks in advance,
> > Johnf
>
> Can you give a more precise example please? I don't get what you really
> need. What I understand is that you want 1 record back for each
> sessionid with the earliest and latest class_date.
>
> I've done the following:
>
> 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
> (7 rows)
>
> and then:
>
> lem=# select min(pkid) as pkid
> lem-# ,min(class_date) as class_date1
> lem-# ,max(class_date) as class_date2
> lem-# ,sessionid
> lem-# from mytable
> lem-# group by sessionid;
> pkid | class_date1 | class_date2 | sessionid
> ------+---------------------+---------------------+-----------
> 5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 | 2103
> 3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2102
> 1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 | 2101
> (3 rows)
>
> Is this what you need or is there something else? Can you give more
> sample data and the result you expect from it?
>
>
> Cheers, Leo
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.
THE PROBLEM:
Since the classes are in a single row per class I need a way to get two
classes into a single row to allow the report writer to print two classes per
row. I don't know how too!
In general the sessionid will be the same but it is not the only thing I'm
using to find the student.
The "essess" table is the available sessions.
The "esclass" contains the classes and any reschedule classes with a FK into
essess
The 'esenroll' has the student, the session.
This is converted from an old Visual Fox Pro program.
The actual tables in question
The sessions:
CREATE TABLE essess
(
pkid serial NOT NULL,
sessionid_do_not_use integer,
courseid integer,
instrid integer,
sequenceid integer,
began date,
ended date,
cancelled boolean,
name_1 character varying(35),
locationid integer,
facility character varying(35),
availseats numeric(5),
depart integer,
stop_close boolean DEFAULT false,
langid integer,
monday boolean DEFAULT false,
tuesday boolean DEFAULT false,
wedesday boolean DEFAULT false,
thursday boolean DEFAULT false,
friday boolean DEFAULT false,
saturday boolean DEFAULT false,
sunday boolean DEFAULT false,
end_time character varying(10),
start_time character varying(10),
note character varying,
eligiblecourses text,
topic integer,
total_hours numeric(5,1) DEFAULT 0.0,
total_classes integer DEFAULT 0,
CONSTRAINT essess_pkey PRIMARY KEY (pkid)
)
The class table
CREATE TABLE esclass
(
pkid serial NOT NULL,
classid_do_not_use integer,
courseid integer,
languageid integer,
zoneid integer,
name_1 character varying(30),
schedule date,
weekday character varying(10),
sessionid integer,
starthr numeric(2),
startmin numeric(2),
am_or_pm numeric(1),
instrid integer,
facility character varying(35),
classseq numeric(5),
depart integer,
locationid integer,
starttime character varying(10) DEFAULT '9:00AM'::character varying,
endtime character varying(10) DEFAULT '3:30PM'::character varying,
isholiday boolean DEFAULT false,
atten_taken boolean DEFAULT false,
fk_rescheduled_class integer DEFAULT 0, -- if the class is rescheduled the
new class pkid is stored here
CONSTRAINT esclass_pkey PRIMARY KEY (pkid)
)
The enrollment:
CREATE TABLE esenroll
(
pkid serial NOT NULL,
enrollid_do_not_use integer,
agencyid integer,
clientid integer,
caseno character varying(13),
referred date,
reminded date,
warned date,
earliest date,
latest date,
enrolled date,
holdtill date,
musent date,
mucallrcd date,
scheduled date,
deadline date,
completed date,
failed date,
notify_1 boolean,
newfld character varying(10),
sessionid integer,
onhold boolean,
not_sched boolean,
depart integer,
enroll_dte date,
final_1 date,
confirmltr date,
agnoteltr date,
schedltr date,
makeupltr date,
hd_all_mai boolean,
fk_clientid integer,
fk_escourts integer,
fk_essess integer,
fk_escourse integer,
fk_topicid integer,
fk_pccode integer DEFAULT 0,
ref_no character varying(60),
mustenrollby date,
req_hrs integer DEFAULT 0,
refer_again boolean DEFAULT false,
ret_to_court date DEFAULT now(),
rereferred date,
yntbd character(1) DEFAULT ''::bpchar,
restitution numeric(10,2) DEFAULT 0,
terminated date,
course_cost numeric(10,2) DEFAULT 0.00,
community_service integer DEFAULT 0,
cumminity_tobedeter boolean DEFAULT true,
restitution_tobedeter boolean DEFAULT true,
note text,
course_note text,
restitution_note text,
community_note text,
previous_enroll integer DEFAULT 0,
inactive date,
locked boolean DEFAULT false,
refer_atten_hrs numeric(10,1) DEFAULT 0.0,
hold_until date,
moved_to integer DEFAULT 0,
fk_esagcontacts integer,
mandatory boolean DEFAULT false,
term_note text DEFAULT ''::text,
comp_note text DEFAULT ''::text,
enroll_note text DEFAULT ''::text,
course_pay_sched text,
CONSTRAINT esenroll_pkey PRIMARY KEY (pkid),
CONSTRAINT fk_esclient1 FOREIGN KEY (fk_clientid)
REFERENCES esclient (pkid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
2009-08-06 13:42 ` Re: two records per row from query Leo Mannhart <leo.mannhart@beecom.ch>
2009-08-06 15:46 ` Re: two records per row from query John <jfabiani@yolo.com>
@ 2009-08-07 09:50 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-08-07 13:28 ` Re: two records per row from query John <jfabiani@yolo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Leo Mannhart @ 2009-08-07 09:50 UTC (permalink / raw)
To: John <jfabiani@yolo.com>; +Cc: 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-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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: two records per row from query
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
2009-08-06 13:42 ` Re: two records per row from query Leo Mannhart <leo.mannhart@beecom.ch>
2009-08-06 15:46 ` Re: two records per row from query John <jfabiani@yolo.com>
2009-08-07 09:50 ` Re: two records per row from query Leo Mannhart <leo.mannhart@beecom.ch>
@ 2009-08-07 13:28 ` John <jfabiani@yolo.com>
0 siblings, 0 replies; 8+ messages in thread
From: John @ 2009-08-07 13:28 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-08-07 13:28 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-05 23:51 two records per row from query John <jfabiani@yolo.com>
2009-08-06 05:21 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-06 13:48 ` John <jfabiani@yolo.com>
2009-08-06 14:21 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-06 13:42 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-08-06 15:46 ` John <jfabiani@yolo.com>
2009-08-07 09:50 ` Leo Mannhart <leo.mannhart@beecom.ch>
2009-08-07 13:28 ` John <jfabiani@yolo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox