Add columns from joined table to crosstab query

user_9203459 0
Viewed 51

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 lastname email accepted
1 A a -- true
2 B b -- true
3 C b -- true
4 D d -- true
Guest
id name accepted id_person (foreign_key -> person.id)
1 AGuest1 true 1
2 BGuest1 true 2
3 BGuest2 true 2
4 CGuest1 true 3
5 CGuest2 false 3
6 CGuest3 false 3

A person can have multiple guests accompanying him/her.

I can generate the following result using the following crosstab query:

Person Name Guest 1 Name Guest 2 Name Guest 3 Name
A AGuest1 -- --
B BGuest1 BGuest2 --
C CGuest1 CGuest2 CGuest3 
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));

But I also want to include extra information from the table to the crosstab query, plus include person with no guests, so it gives the following result:

Person Full Name Person Accepted Guest 1 Name Accepted Guest 2 Name Accepted Guest 3 Name Accepted
Aa true AGuest1 true -- -- -- --
Bb true BGuest1 true BGuest2 true -- --
Cc true CGuest1 true CGuest2 false CGuest3 false
Dd true -- -- -- -- -- --
  • Using the table name in the value part of the crosstab, and specifying the column in the as part, throws an error
  • Also trying to specify additional columns in the crosstab query also throws an error

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

crosstab postgresql sql
1 Answers

You can first assign a row number to each guest row by grouping on the person id. Then join back with the Person table and get each column relative to the number of guest and accepted value using the CASE statement. In order to remove the null values you can aggregate with the MAX aggregation function and group on the remaining columns.

SELECT CONCAT(p.name, p.lastname)                  AS PersonFullName,
       p.accepted                                  AS PersonAccepted,
       MAX(CASE WHEN g.rn = 1 THEN g.name     END) AS Guest1Name,
       MAX(CASE WHEN g.rn = 1 THEN g.accepted END) AS Guest1Accepted,
       MAX(CASE WHEN g.rn = 2 THEN g.name     END) AS Guest2Name,
       MAX(CASE WHEN g.rn = 2 THEN g.accepted END) AS Guest2Accepted,
       MAX(CASE WHEN g.rn = 3 THEN g.name     END) AS Guest3Name,
       MAX(CASE WHEN g.rn = 3 THEN g.accepted END) AS Guest3Accepted
FROM       Person p
LEFT JOIN  (SELECT *, 
                   ROW_NUMBER() OVER(PARTITION BY id_person ORDER BY id) AS rn
            FROM Guest) g
        ON p.id = g.id_person
GROUP BY PersonFullName, 
         PersonAccepted
ORDER BY PersonFullName

Check the demo here.

user_124928900
Related