Thread: 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
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,
CASEWHEN 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
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
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