Skip site navigation (1) Skip section navigation (2)

crosstabs

From: "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: crosstabs
Date: 2004-02-19 16:02:10
Message-ID: 6C0CF58A187DA5479245E0830AF84F420AFC0E@poweredge.attiksystem.ch (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-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



Responses

pgsql-novice by date

Next:From: daqDate: 2004-02-19 17:23:53
Subject: Re: [SQL] crosstabs
Previous:From: joseph speigleDate: 2004-02-19 15:10:21
Subject: Re: Cannot insert a duplicate key into unique index

pgsql-sql by date

Next:From: Stephan SzaboDate: 2004-02-19 16:20:12
Subject: Re: Compiling pl/pgsql functions
Previous:From: Sumita BiswasDate: 2004-02-19 15:10:00
Subject: Re: Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group