Add columns from joined table to crosstab query-CodePudding

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!

CodePudding user response：

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 
                ELSE NULL END   ) AS Guest1Name,
       MAX(CASE WHEN g.rn = 1 THEN g.accepted 
                ELSE NULL END   ) AS Guest1Accepted,
       MAX(CASE WHEN g.rn = 2 THEN g.name 
                ELSE NULL END   ) AS Guest2Name,
       MAX(CASE WHEN g.rn = 2 THEN g.accepted 
                ELSE NULL END   ) AS Guest2Accepted,
       MAX(CASE WHEN g.rn = 3 THEN g.name 
                ELSE NULL END   ) AS Guest3Name,
       MAX(CASE WHEN g.rn = 3 THEN g.accepted 
                ELSE NULL 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

Try it here.