pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcrosstabs
6+ messages / 5 participants
[nested] [flat]
* crosstabs
@ 2004-02-19 16:02 Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 3 replies; 6+ messages in thread
From: Philippe Lang @ 2004-02-19 16:02 UTC (permalink / raw)
To: pgsql-sql
Hello,
I need to do something similar to a cross tabulation, but without any
aggregation.
Here is an example:
------------------------------------------------------------
-- TABLE DEFINITION
------------------------------------------------------------
CREATE TABLE public.type
(
code char(1) PRIMARY KEY,
type varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.master
(
id int4 PRIMARY KEY,
master_name varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.detail
(
id int4 PRIMARY KEY,
code_type char(1) REFERENCES public.type,
id_master int4 REFERENCES public.master,
detail_name varchar(30)
) WITHOUT OIDS;
------------------------------------------------------------
-- DATA
------------------------------------------------------------
INSERT INTO public.type VALUES('A', 'TA');
INSERT INTO public.type VALUES('B', 'TB');
INSERT INTO public.type VALUES('C', 'TC');
INSERT INTO public.master VALUES(1, 'M1');
INSERT INTO public.master VALUES(2, 'M2');
INSERT INTO public.master VALUES(3, 'M3');
INSERT INTO public.detail VALUES(1, 'A', 1, 'M1, D1');
INSERT INTO public.detail VALUES(2, 'B', 1, 'M1, D2');
INSERT INTO public.detail VALUES(3, 'A', 1, 'M1, D3');
INSERT INTO public.detail VALUES(4, 'C', 1, 'M1, D4');
INSERT INTO public.detail VALUES(5, 'C', 2, 'M2, D1');
INSERT INTO public.detail VALUES(6, 'A', 3, 'M3, D1');
INSERT INTO public.detail VALUES(7, 'A', 3, 'M3, D2');
INSERT INTO public.detail VALUES(8, 'B', 3, 'M3, D3');
INSERT INTO public.detail VALUES(9, 'A', 3, 'M3, D4');
INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');
INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');
INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7');
------------------------------------------------------------
-- QUERY
------------------------------------------------------------
SELECT
master_name,
detail_name,
type
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;
------------------------------------------------------------
The result of that is:
----------------------------------
master_name | detail_name | type |
----------------------------------
M1 | M1, D1 | TA |
M1 | M1, D2 | TB |
M1 | M1, D3 | TA |
M1 | M1, D4 | TC |
M2 | M2, D1 | TC |
M3 | M3, D1 | TA |
M3 | M3, D2 | TA |
M3 | M3, D3 | TB |
M3 | M3, D4 | TA |
M3 | M3, D5 | TB |
M3 | M3, D6 | TC |
M3 | M3, D7 | TC |
----------------------------------
I need something like this:
----------------------------------------
master_name | TA | TB | TC |
----------------------------------------
M1 | M1, D1 | | |
M1 | | M1, D2 | |
M1 | M1, D3 | | |
M1 | | | M1, D4 |
M2 | | | M2, D1 |
M3 | M3, D1 | | |
M3 | M3, D2 | | |
M3 | | M3, D3 | |
M3 | M3, D4 | | |
M3 | | M3, D5 | |
M3 | | | M3, D6 |
M3 | | | M3, D7 |
----------------------------------------
Does anyone know how to do that in Postgresql? I run version 7.3.4.
Thanks for any idea you might have.
Philippe Lang
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] crosstabs
@ 2004-02-19 17:23 daq <daq@ugyvitelszolgaltato.hu>
parent: Philippe Lang <philippe.lang@attiksystem.ch>
2 siblings, 1 reply; 6+ messages in thread
From: daq @ 2004-02-19 17:23 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-novice@postgresql.org
PL> ------------------------------------------------------------
PL> -- QUERY
PL> ------------------------------------------------------------
PL> SELECT
PL> master_name,
PL> detail_name,
PL> type
PL> FROM
PL> master INNER JOIN detail
PL> ON master.id = detail.id_master
PL> INNER JOIN type
PL> ON detail.code_type = type.code
PL> ORDER by master.id, detail.id;
PL> ------------------------------------------------------------
PL> The result of that is:
PL> ----------------------------------
PL> master_name | detail_name | type |
PL> ----------------------------------
PL> M1 | M1, D1 | TA |
PL> M1 | M1, D2 | TB |
PL> M1 | M1, D3 | TA |
PL> M1 | M1, D4 | TC |
PL> M2 | M2, D1 | TC |
PL> M3 | M3, D1 | TA |
PL> M3 | M3, D2 | TA |
PL> M3 | M3, D3 | TB |
PL> M3 | M3, D4 | TA |
PL> M3 | M3, D5 | TB |
PL> M3 | M3, D6 | TC |
PL> M3 | M3, D7 | TC |
PL> ----------------------------------
PL> I need something like this:
PL> ----------------------------------------
PL> master_name | TA | TB | TC |
PL> ----------------------------------------
PL> M1 | M1, D1 | | |
PL> M1 | | M1, D2 | |
PL> M1 | M1, D3 | | |
PL> M1 | | | M1, D4 |
PL> M2 | | | M2, D1 |
PL> M3 | M3, D1 | | |
PL> M3 | M3, D2 | | |
PL> M3 | | M3, D3 | |
PL> M3 | M3, D4 | | |
PL> M3 | | M3, D5 | |
PL> M3 | | | M3, D6 |
PL> M3 | | | M3, D7 |
PL> ----------------------------------------
PL> Does anyone know how to do that in Postgresql? I run version 7.3.4.
PL> Thanks for any idea you might have.
PL> Philippe Lang
Maybe you can use the CASE construct.
select mastername, case when type='TA' then detail_name else '' end as ta, case .... as tb, case ... as ts from ...
I don't try this, but maybe...
DAQ
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstabs
@ 2004-02-19 17:26 Richard Huxton <dev@archonet.com>
parent: Philippe Lang <philippe.lang@attiksystem.ch>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2004-02-19 17:26 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; pgsql-sql
On Thursday 19 February 2004 16:02, Philippe Lang wrote:
> Hello,
>
> I need to do something similar to a cross tabulation, but without any
> aggregation.
Have a look in the contrib/ folder of the source distro (or your contrib
package). There are some set-returning tablefunc examples. Also you might
want to look at the excellent set-returning function article on
http://techdocs.postgresql.org/
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstabs
@ 2004-02-19 17:28 Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 0 replies; 6+ messages in thread
From: Philippe Lang @ 2004-02-19 17:28 UTC (permalink / raw)
To: pgsql-sql
Here is a solution:
------------------------------------------------------------
-- QUERY
------------------------------------------------------------
SELECT
master_name,
CASE WHEN type = 'TA' THEN detail_name END as TA,
CASE WHEN type = 'TB' THEN detail_name END as TB,
CASE WHEN type = 'TC' THEN detail_name END as TC
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;
------------------------------------------------------------
I'm not sure if we could do that without CASE... END...
-----Message d'origine-----
De : pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] De la part de Philippe Lang
Envoyé : jeudi, 19. février 2004 17:02
À : pgsql-sql@postgresql.org
Objet : [SQL] crosstabs
Hello,
I need to do something similar to a cross tabulation, but without any aggregation.
Here is an example:
------------------------------------------------------------
-- TABLE DEFINITION
------------------------------------------------------------
CREATE TABLE public.type
(
code char(1) PRIMARY KEY,
type varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.master
(
id int4 PRIMARY KEY,
master_name varchar(30)
) WITHOUT OIDS;
CREATE TABLE public.detail
(
id int4 PRIMARY KEY,
code_type char(1) REFERENCES public.type,
id_master int4 REFERENCES public.master,
detail_name varchar(30)
) WITHOUT OIDS;
------------------------------------------------------------
-- DATA
------------------------------------------------------------
INSERT INTO public.type VALUES('A', 'TA');
INSERT INTO public.type VALUES('B', 'TB');
INSERT INTO public.type VALUES('C', 'TC');
INSERT INTO public.master VALUES(1, 'M1');
INSERT INTO public.master VALUES(2, 'M2');
INSERT INTO public.master VALUES(3, 'M3');
INSERT INTO public.detail VALUES(1, 'A', 1, 'M1, D1');
INSERT INTO public.detail VALUES(2, 'B', 1, 'M1, D2');
INSERT INTO public.detail VALUES(3, 'A', 1, 'M1, D3');
INSERT INTO public.detail VALUES(4, 'C', 1, 'M1, D4');
INSERT INTO public.detail VALUES(5, 'C', 2, 'M2, D1');
INSERT INTO public.detail VALUES(6, 'A', 3, 'M3, D1');
INSERT INTO public.detail VALUES(7, 'A', 3, 'M3, D2');
INSERT INTO public.detail VALUES(8, 'B', 3, 'M3, D3');
INSERT INTO public.detail VALUES(9, 'A', 3, 'M3, D4');
INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');
INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');
INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7');
------------------------------------------------------------
-- QUERY
------------------------------------------------------------
SELECT
master_name,
detail_name,
type
FROM
master INNER JOIN detail
ON master.id = detail.id_master
INNER JOIN type
ON detail.code_type = type.code
ORDER by master.id, detail.id;
------------------------------------------------------------
The result of that is:
----------------------------------
master_name | detail_name | type |
----------------------------------
M1 | M1, D1 | TA |
M1 | M1, D2 | TB |
M1 | M1, D3 | TA |
M1 | M1, D4 | TC |
M2 | M2, D1 | TC |
M3 | M3, D1 | TA |
M3 | M3, D2 | TA |
M3 | M3, D3 | TB |
M3 | M3, D4 | TA |
M3 | M3, D5 | TB |
M3 | M3, D6 | TC |
M3 | M3, D7 | TC |
----------------------------------
I need something like this:
----------------------------------------
master_name | TA | TB | TC |
----------------------------------------
M1 | M1, D1 | | |
M1 | | M1, D2 | |
M1 | M1, D3 | | |
M1 | | | M1, D4 |
M2 | | | M2, D1 |
M3 | M3, D1 | | |
M3 | M3, D2 | | |
M3 | | M3, D3 | |
M3 | M3, D4 | | |
M3 | | M3, D5 | |
M3 | | | M3, D6 |
M3 | | | M3, D7 |
----------------------------------------
Does anyone know how to do that in Postgresql? I run version 7.3.4.
Thanks for any idea you might have.
Philippe Lang
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: crosstabs
@ 2004-02-19 18:58 Joe Conway <mail@joeconway.com>
parent: Philippe Lang <philippe.lang@attiksystem.ch>
2 siblings, 0 replies; 6+ messages in thread
From: Joe Conway @ 2004-02-19 18:58 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
Philippe Lang wrote:
> I need to do something similar to a cross tabulation, but without any
> aggregation.
See the crosstab() function found in contrib/tablefunc
Joe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] crosstabs
@ 2004-02-22 18:54 joseph speigle <joe.speigle@jklh.us>
parent: daq <daq@ugyvitelszolgaltato.hu>
0 siblings, 0 replies; 6+ messages in thread
From: joseph speigle @ 2004-02-22 18:54 UTC (permalink / raw)
To: pgsql-novice@postgresql.org
On Thu, Feb 19, 2004 at 06:23:53PM +0100, daq wrote:
>
>
> PL> ------------------------------------------------------------
> PL> -- QUERY
> PL> ------------------------------------------------------------
> PL> SELECT
> PL> master_name,
> PL> detail_name,
> PL> type
>
> PL> FROM
> PL> master INNER JOIN detail
> PL> ON master.id = detail.id_master
>
> PL> INNER JOIN type
> PL> ON detail.code_type = type.code
>
> PL> ORDER by master.id, detail.id;
>
> PL> ------------------------------------------------------------
>
>
> PL> The result of that is:
>
> PL> ----------------------------------
> PL> master_name | detail_name | type |
> PL> ----------------------------------
> PL> M1 | M1, D1 | TA |
> PL> M1 | M1, D2 | TB |
> PL> M1 | M1, D3 | TA |
> PL> M1 | M1, D4 | TC |
> PL> M2 | M2, D1 | TC |
> PL> M3 | M3, D1 | TA |
> PL> M3 | M3, D2 | TA |
> PL> M3 | M3, D3 | TB |
> PL> M3 | M3, D4 | TA |
> PL> M3 | M3, D5 | TB |
> PL> M3 | M3, D6 | TC |
> PL> M3 | M3, D7 | TC |
> PL> ----------------------------------
>
>
> PL> I need something like this:
>
> PL> ----------------------------------------
> PL> master_name | TA | TB | TC |
> PL> ----------------------------------------
> PL> M1 | M1, D1 | | |
> PL> M1 | | M1, D2 | |
> PL> M1 | M1, D3 | | |
> PL> M1 | | | M1, D4 |
> PL> M2 | | | M2, D1 |
> PL> M3 | M3, D1 | | |
> PL> M3 | M3, D2 | | |
> PL> M3 | | M3, D3 | |
> PL> M3 | M3, D4 | | |
> PL> M3 | | M3, D5 | |
> PL> M3 | | | M3, D6 |
> PL> M3 | | | M3, D7 |
> PL> ----------------------------------------
>
>
> PL> Does anyone know how to do that in Postgresql? I run version 7.3.4.
>
> PL> Thanks for any idea you might have.
>
> PL> Philippe Lang
>
> Maybe you can use the CASE construct.
>
> select mastername, case when type='TA' then detail_name else '' end as ta, case .... as tb, case ... as ts from ...
>
> I don't try this, but maybe...
>
> DAQ
the part which actually does it is the last select statement.
drop table master cascade;
create table master (
id int4 UNIQUE,
master_name varchar(10)
);
drop table type cascade;
create table type (
code serial unique,
type varchar(10)
);
drop table detail cascade;
create table detail (
id serial unique,
master_id int4 REFERENCES master(id),
detail_name varchar(10),
type_code int4 REFERENCES type(code)
);
insert into master (id, master_name) values ('1','M1');
insert into master (id, master_name) values ('2','M2');
insert into master (id, master_name) values ('3','M3');
insert into type (code,type) values (1,'TA');
insert into type (code,type) values (2,'TB');
insert into type (code,type) values (3,'TC');
insert into detail (master_id, detail_name, type_code) values ('1','M1,D1',1);
insert into detail (master_id, detail_name, type_code) values ('1','M1,D3',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D1',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D3',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D4',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D5',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D6',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D8',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D7',1);
insert into detail (master_id, detail_name, type_code) values ('1','M1,D2',2);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D2',2);
insert into detail (master_id, detail_name, type_code) values (2,'M2,D4',3);
insert into detail (master_id, type_code) values (2,3);
insert into detail (master_id, type_code) values (1,3);
insert into detail (master_id, detail_name, type_code) values (2,'M2,D4',3);
drop view TA;
drop view TB;
drop view TC;
CREATE VIEW TA AS SELECT detail.id AS detail_id, detail_name AS TA, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TA') AND detail.master_id=master.id;
SELECT * FROM TA;
CREATE VIEW TB AS SELECT detail.id AS detail_id, detail_name AS TB, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TB') AND detail.master_id=master.id;
SELECT * FROM TB;
CREATE VIEW TC AS SELECT detail.id AS detail_id, detail_name AS TC, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TC') AND detail.master_id=master.id;
SELECT * FROM TA;
SELECT * FROM TB;
SELECT * FROM TC;
SELECT master.master_name, TA.TA, TB.TB, TC.TC
FROM TA
FULL OUTER JOIN TB
ON ta.detail_id = tb.detail_id
FULL OUTER JOIN TC
ON ta.detail_id = tc.detail_id
JOIN master ON master.id = ta.master_id OR master.id = tb.master_id OR master.id = tc.master_id;
which gives:
master_name | ta | tb | tc
-------------+-------+-------+-------
M1 | M1,D1 | |
M1 | M1,D3 | |
M3 | M3,D1 | |
M3 | M3,D3 | |
M3 | M3,D4 | |
M3 | M3,D5 | |
M3 | M3,D6 | |
M3 | M3,D8 | |
M3 | M3,D7 | |
M1 | | M1,D2 |
M3 | | M3,D2 |
M2 | | | M2,D4
M2 | | |
M1 | | |
M2 | | | M2,D4
(15 rows)
joe
--
speigle
www.sirfsup.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-02-22 18:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-19 16:02 crosstabs Philippe Lang <philippe.lang@attiksystem.ch>
2004-02-19 17:23 ` daq <daq@ugyvitelszolgaltato.hu>
2004-02-22 18:54 ` joseph speigle <joe.speigle@jklh.us>
2004-02-19 17:26 ` Richard Huxton <dev@archonet.com>
2004-02-19 18:58 ` Joe Conway <mail@joeconway.com>
2004-02-19 17:28 Re: crosstabs Philippe Lang <philippe.lang@attiksystem.ch>
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