Fetch results of joined tables as columns in postgres

user_9203459 0
Viewed 38

I trying to figure out a way to generate a SQL query, to be used in a view, to generate an Excel like format for parent-child relationship.

I have the following tables

Person
id name email accepted
1 A -- --
2 B -- --
3 C -- --
Guest
id name accepted id_person (foreign_key -> person.id)
1 AGuest1 --- 1
2 BGuest1 --- 2
3 BGuest2 --- 2
4 CGuest1 --- 3
5 CGuest2 --- 3
6 CGuest3 --- 3

A person can have multiple guests accompanying him/her.

What I want to do is generate a SQL that gives me the following result:

Person Name Guest 1 Name Guest 2 Name Guest 3 Name
A AGuest1 -- --
B BGuest1 BGuest2 --
C CGuest1 CGuest2 CGuest3

I can generate two separate queries that will generate a list of rows with that information but I am struggling to generate multiple columns based on the information that I have.

I have looked into crosstab() for postgres, but so far I have no luck with generating anything that would like this.

Any help or pointers in the right direction would be helpful, thank you!

crosstab postgresql sql
1 Answers

You can use CROSSTAB:

SELECT *
FROM CROSSTAB (
    'SELECT p.name, g.name, g.name
    FROM person p JOIN guest g
    ON p.id = g.id_person
    ORDER BY 1,2')
    AS ("Person Name" varchar(100), "Guest 1 Name" varchar(100),
    "Guest 2 Name" varchar(100), "Guest 3 Name" varchar(100));

Please note: This will work for 1 to 3 guest like you described. In case more guests per person are possible, you need to extend this.

I created a working example here: db<>fiddle

user_187948260
Related