crosstab query provides unexpected results

Question

I got a bit confused by the crosstab() function inside PostgreSQL's tablefunc extension. The problem is that I want not a standard Name-Category-Value scheme, but something like Name-Attribute of Name-One more attribute-Category-Value. It doesn't seemed like a problem but soon I realised that with my DB scheme it wouldn't be as easy as I thought.

Background: I have 3 tables I want to use: Users, UserEvents, QuestionaryAnswers.

The Users table contains people
UserEvents contains events that happened with this people (with FK reference to Users)
and QuestionaryAnswers that contains answers to Questions which are referencing to UserEventId via FK.

So, tables look like this:

create table "Users"("Id" int, "AttrId" int, "GroupId" int);
create table "UserEvents"("Id" int, "UserId" int, "Status" varchar(20), "EventId" int);
create table "QuestionaryAnswers"("UserEventId" int, "QuestionaryItemId" int, "AnswerItemId" int);

Users:

INSERT INTO "public"."Users"("Id", "AttrId", "GroupId") 
VALUES (1, 1, 12587), (2, 1, 11092);

UserEvents:

INSERT INTO "public"."UserEvents"("Id", "UserId", "Status", "EventId") 
VALUES (142, 1, 'Checked', 2), (143, 1, 'Created', 1), (144, 2, 'Done', 2);

And QuestionaryAnswers:

INSERT INTO "public"."QuestionaryAnswers"("UserEventId", "QuestionaryItemId", "AnswerItemId") 
VALUES ('142', 1, 2),
('142', 4, 16),
('142', 5, 25),
('143', 12, 99);
('144', 12, 100);

Well, that's where problems are appearing. This is my crosstab query for now:

SELECT *
FROM crosstab(' SELECT "UserEvents"."UserId", "QuestionaryAnswers"."UserEventId", "Users"."AttrId", "Users"."GroupId", "QuestionaryAnswers"."QuestionaryItemId", "AnswerItems"."Name"
 FROM "QuestionaryAnswers"
    LEFT JOIN "AnswerItems" ON "QuestionaryAnswers"."AnswerItemId" = "AnswerItems"."Id" 
    LEFT JOIN "UserEvent" ON "QuestionaryAnswers"."UserEventId" = "UserEvents"."Id" 
    LEFT JOIN "Users" ON "UserEvents"."UserId" = "Users"."Id"
    ORDER BY 1, 2'::text, 
'SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 20 UNION SELECT 21 ORDER BY 1'::text) 
crosstab("UserId" integer, "UserEventId" uuid,
"AttrId" integer, "GroupId" integer,
"Question1" text, "2" text, "3" text, "4" text,
"5" text, "6" text, "7" text, "8" text, "9" text,
"10" text, "11" text, "12" text, "13" text, "14" text,
"15" text, "16" text, "17" text, "18" text, "19" text)

And here all seems quite straightforward, except one little detail - categories\columns\questions are linked to different UserEventId's, so basically UserEventId is picked randomly (by ORDER clause), and because of that Status attribute from the UserEvents table will be picked randomly too. What I want to see is separate UserEventId and Status fields that are derived by EventId, so there would be UserEventId_1 and UserEventId_2 IDs, and probably linked fields too, e.g Status_1 and Status_2, like this:

UserId | UserEventId_EventId1 | UserEventId_EventId2 | AttrId | GroupId | Question1 | Question3 | Question4 | Question5 | Question12
-------+----------------------+----------------------+--------+---------+-----------+-----------+-----------+-----------+-----------
     1 |                  143 |                  142 |      1 |   12587 |           |           |        16 |        25 |         99
     2 |                      |                  144 |      1 |   11092 |           |           |           |           |        144

So the questions are:

How can I make a "category" crosstab column based on the foreign key value? I can't figure that out, probably because it's different "stacks" of categories - events and questions.
The whole idea of this is a bit wrong - crosstab isn't intended to show data in format that I want, so I'll describle the goal. I need a table to run simple comparative queries "how much people answered to question 1 like this and answered to question 2 like this", but I also need to filter this on "UserEvents" Status field and "Users" GroupId field, so I need either EventID or Status inside the table. Am I missing some opportunity to do this simpler or some ability to show this data in crosstab? P.S I am using PostgreSQL 11.1

Yes with what you write I don't see why you are doing a cross join — Daniel E.
So I edited my question a bit to have some form of desired results. Is it OK now? — Pavel Nasevich
Your goal would be much clearer if you could simply throw here the sample of the table that you're trying to get — user3307073
I set up a dbfiddle (achieving your result without crosstab) dbfiddle.uk/… But I don't really understand what's your problem. Nevertheless: Where does the value "144" in the last column come from? — S-Man

Unknown Unknown · Accepted Answer · 2018-12-21T14:28:54

So far, what you have described may be achieved without crosstab invocation:

select u1."Id" as "UserId", u1."GroupId", e1."Status", 
    q1."QuestionaryItemId", q1."AnswerItemId", count(*)
from "Users" u1
left join "UserEvents" e1
on u1."Id" = e1."UserId"
left join "QuestionaryAnswers" q1
on q1."UserEventId"=e1."Id"
group by 1,2,3,4,5

You may check out the result here.

crosstab query provides unexpected results

1 Answers