Turning rows to columns in postgres

Question

I have one table with rental contracts. (Postgres v10.18)

Like this:

Table Rental
Id          Start       Main_tenant_id  Obect_id
101011          1.1.2021    1000            200
100222          1.1.2021    2050            210

If the Object has more than one Tenant the other ones a saved in a separate Table like this:

Table Rental_extra
Id          rental_id   xtra_tenant
20001           100222      3000
20002           100222      2700
20003           100222      2800

And i have a Person table like this:

Table Person
Id          first_name      last_name
1000            Peter           Mcdonald
2050            Dan         Hunt
3000            Steve           Cole
2700            Ian         Wright
2800            William         Pears

Now i need to get this output:

Goal
Id      tenant 1        tenant 2        tenant 3        tenant 4
101011      Peter Mcdonald  null            null            null
100222      Dan Hunt        Steve Cole      Ian Wright      William Pears

What's the best way? I tried with some crosstab example but couldn't make it work.

We very much prefer data as *text*. One cannot copy/paste from an image. — Erwin Brandstetter, Oct 15 '21 at 15:42

score 1 · Accepted Answer · answered Oct 18 '21 at 21:22

SELECT *
FROM   crosstab(
$$
SELECT t.id, tenant_nr, concat_ws(' ', p.first_name, p.last_name)
FROM  (
   SELECT id, 0 AS tenant_nr, main_tenant_id AS tenant_id
   FROM   rental
   UNION ALL
   SELECT rental_id, row_number() OVER (PARTITION BY rental_id ORDER BY id), xtra_tenant
   FROM   extra_tenant 
   ) t
JOIN   person p ON p.id = t.tenant_id
ORDER  BY 1 DESC, t.tenant_nr
$$
) AS goal(id int, tenant_1 text, tenant_2 text, tenant_3 text, tenant_4 text);

db<>fiddle here

Detailed explanation here:

Turning rows to columns in postgres

1 Answers1