pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: joseph speigle <joe.speigle@jklh.us>
To: pgsql-novice@postgresql.org
Subject: Re: [SQL] crosstabs
Date: Sun, 22 Feb 2004 12:54:59 -0600
Message-ID: <20040222185459.GA12077@www.sirfsup.com> (raw )
In-Reply-To: <94113164570.20040219182353@ugyvitelszolgaltato.hu >
References: <6C0CF58A187DA5479245E0830AF84F420AFC0E@poweredge.attiksystem.ch >
<94113164570.20040219182353@ugyvitelszolgaltato.hu >
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
view thread (6+ messages)
Message-ID: <20040222185459.GA12077@www.sirfsup.com>
Permalink: ../20040222185459.GA12077@www.sirfsup.com/
Also on: postgresql.org/message-id/20040222185459.GA12077@www.sirfsup.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joe.speigle@jklh.us, pgsql-novice@postgresql.org
Subject: Re: [SQL] crosstabs
In-Reply-To: <20040222185459.GA12077@www.sirfsup.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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