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 : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Philippe Lang Envoyé : jeudi, 19. février 2004 17:02 À : [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
