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
– Erwin Brandstetter, Commented Oct 15, 2021 at 15:42

Erwin Brandstetter · Accepted Answer · 2021-10-18 21:22:03Z

1

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:

answered Oct 18, 2021 at 21:22

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Turning rows to columns in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related