1

I'm trying to create pivot tables on PostgreSQL.

I successfully created my first example. But I have on problem:

I use the two-parameter form of crosstab, which takes a query for the data and a second query for the categories.

I also have to provide a list of category names in the AS statement.

But how can I make sure that they are in the same order?

The documentation uses SELECT DISTINCT category FROM table. But that can't guarantee me any order of the categories, right?


I have three test objects/experiments X_E001-3 with three fields/categories/attributes:

  • MR_Type (out of: Tw2, T2w, MRx)
  • groundTruth (out of: head, liver, abdomen)
  • origin (out of: Hamburg, Cambridge, Paris)
CREATE TABLE x_experiment_fields (
    x_experiment_id text NOT NULL,
    field text NOT NULL,
    value text NOT NULL
);

INSERT INTO x_experiment_fields VALUES ('X_E001', 'MR_Type', 'T2w');
INSERT INTO x_experiment_fields VALUES ('X_E001', 'organ', 'liver');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'MR_Type', 'Tw2');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'organ', 'abdomen');
INSERT INTO x_experiment_fields VALUES ('X_E002', 'origin', 'Hamburg');
INSERT INTO x_experiment_fields VALUES ('X_E001', 'origin', 'Paris');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'origin', 'Cambridge');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'organ', 'head');
INSERT INTO x_experiment_fields VALUES ('X_E003', 'MR_Type', 'MRx');

I then make may query as proposed:

SELECT * FROM crosstab(
    'SELECT x_experiment_id, field, value FROM x_experiment_fields ORDER BY 1',
    'SELECT DISTINCT field FROM x_experiment_fields')
AS (x_experiment_id text, MR_Type text, organ text, origin text);

which outputs:

 x_experiment_id | mr_type | organ     | origin
-----------------+---------+-----------+--------
 X_E001          | liver   | Paris     | T2w
 X_E002          | abdomen | Hamburg   | Tw2
 X_E003          | head    | Cambridge | MRx

which is wrong (or better not intended).

The reason is, that SELECT DISTINCT field FROM x_experiment_fields gives me

 field
-------
organ
origin
MR_Type

whereas I want them in order MR_Type, groundTruth, origin.

Even if I change my order, I can't be sure the query always gives the identical order, right?

I could order the query and my list. But then the order might not me as wanted (and it makes it harder to extend the attributes list later on).

Is there any common use?

Robin

0

Ok, I might have found an answer myself. I learned that I can "emulate" the table generated by the second query by this:

$$VALUES ('MR_Type'), ('groundTruth'), ('origin')$$

This way I have control over the order. And if anything changes I have to add it into the AS statement nevertheless.

I don't want to eliminate the possibility to use maybe another special purpose table to control this mechanism, but for now this would be ok.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.