Re: crosstabs - Mailing list 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,
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