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 | 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!
CodePudding user response：
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