Le portail francophone de la géomatique

SELECT immeubles.*,locataires_numero1_appartF1.*,locataires_numero2_appartF1.*,locataires locataires_numero1_appartF2.*,locataires locataires_numero1_appartF2.*,locataires locataires_numero3_appartF2.* 
FROM immeubles 
LEFT JOIN (SELECT * FROM locataires WHERE type_appart = 'F1' LIMIT 1 OFFSET 0) locataires_numero1_appartF1 ON immeubles.idimmeuble = locataires_numero1_appartF1.idimmeuble
LEFT JOIN (SELECT * FROM locataires WHERE type_appart = 'F1' LIMIT 1 OFFSET 1) locataires_numero2_appartF1 ON immeubles.idimmeuble = locataires_numero2_appartF1.idimmeuble
LEFT JOIN (SELECT * FROM locataires WHERE type_appart = 'F2' LIMIT 1 OFFSET 0) locataires_numero1_appartF2 ON immeubles.idimmeuble = locataires_numero1_appartF2.idimmeuble
LEFT JOIN (SELECT * FROM locataires WHERE type_appart = 'F2' LIMIT 1 OFFSET 1) locataires_numero2_appartF2 ON immeubles.idimmeuble = locataires_numero2_appartF2.idimmeuble
LEFT JOIN (SELECT * FROM locataires WHERE type_appart = 'F2' LIMIT 1 OFFSET 2) locataires_numero3_appartF2 ON immeubles.idimmeuble = locataires_numero3_appartF2.idimmeuble

create table immeubles(
    id serial primary key,
    nom text,
    geom geometry
);

create table locataires (
    id serial primary key,
    id_imm int,
    nom text,
    typ_log text,
    loyer int
);

insert into immeubles(nom) values 
('immeuble 1'),
('immeuble 2'),
('immeuble 3');

insert into locataires (id_imm, nom, typ_log, loyer) values 
(1, 'toto', 'F3', 500),
(1, 'titi', 'F1', 350),
(2, 'tutu', 'F2', 300),
(2, 'tata', 'F1', 200),
(2, 'tyty', 'F4', 750),
(2, 'tete', 'F4', 800),
(2, 'John', 'F5', 900),
(3, 'bill', 'F3', 400),
(3, 'sam', 'F2', 300),
(3, 'luc', 'F5', 1300),
(3, 'eve', 'F5', 1100);

-- pivot sur la table des locataires par immeubles.
-- Les infos d'un locataire sont stockées dans un hstore, puis
-- exprimées en colonnes dans la requete finale
with cross_ as (
    select * from crosstab(
        'select i.id, l.typ_log, hstore(l)::text as loc_info 
        from immeubles i, locataires l 
        where i.id = l.id_imm 
        order by 1,2') 
    as ct(id_imm int, loc1 text, loc2 text, loc3 text, loc4 text, loc5 text)
) select id_imm,
-- locataire 1
(loc1::hstore)->'id' as loc1_id, (loc1::hstore)->'nom' as loc1_nom, 
(loc1::hstore)->'typ_log' as loc1_typ_log, (loc1::hstore)->'loyer' as loc1_loyer, 

-- locataire 2
(loc2::hstore)->'id' as loc2_id, (loc2::hstore)->'nom' as loc2_nom, 
(loc2::hstore)->'typ_log' as loc2_typ_log, (loc2::hstore)->'loyer' as loc2_loyer, 

-- locataire 3
(loc3::hstore)->'id' as loc3_id, (loc3::hstore)->'nom' as loc3_nom, 
(loc3::hstore)->'typ_log' as loc3_typ_log, (loc3::hstore)->'loyer' as loc3_loyer, 

-- locataire 5
(loc4::hstore)->'id' as loc4_id, (loc4::hstore)->'nom' as loc4_nom, 
(loc4::hstore)->'typ_log' as loc4_typ_log, (loc4::hstore)->'loyer' as loc4_loyer, 

-- locataire 5
(loc5::hstore)->'id' as loc5_id, (loc5::hstore)->'nom' as loc5_nom, 
(loc5::hstore)->'typ_log' as loc5_typ_log, (loc5::hstore)->'loyer' as loc5_loyer
from cross_;

 id_imm | loc1_id | loc1_nom | loc1_typ_log | loc1_loyer | loc2_id | loc2_nom | loc2_typ_log | loc2_loyer | loc3_id | loc3_nom | loc3_typ_log | loc3_loyer | loc4_id | loc4_nom | loc4_typ_log | loc4_loyer | loc5_id | loc5_nom | loc5_typ_log | loc5_loyer 
--------+---------+----------+--------------+------------+---------+----------+--------------+------------+---------+----------+--------------+------------+---------+----------+--------------+------------+---------+----------+--------------+------------
      1 | 2       | titi     | F1           | 350        | 1       | toto     | F3           | 500        |         |          |              |            |         |          |              |            |         |          |              | 
      2 | 4       | tata     | F1           | 200        | 3       | tutu     | F2           | 300        | 6       | tete     | F4           | 800        | 5       | tyty     | F4           | 750        | 7       | John     | F5           | 900
      3 | 9       | sam      | F2           | 300        | 8       | bill     | F3           | 400        | 10      | luc      | F5           | 1300       | 11      | eve      | F5           | 1100       |         |          |              | 
(3 rows)