Postgresql star schema pivot query?

Question

I have a medium sized database housed in a star-schema with four outer branches of the star. For one particular piece of visualization software I'm evaluating, I need to query this database into an SPSS file with one of the outer branches pivoted into columns. I'm new to PostgreSQL, so maybe there's some simple way I'm missing.

Here's some example data (with only 2 outer branch tables - attitude & person):

mainTable
id    |   attitudeKey  |    response   |   personKey
1     |       4        |      3        |      2
2     |       1        |      2        |      2
3     |       4        |      1        |      1
4     |       1        |      4        |      1
5     |       5        |      3        |      1

attitudeTable
attitudeKey    |     attitudeName
4              |       happy
1              |       quiet
5              |       grumpy

personTable
personKey      |     personName    |    personAge
1              |        Bob        |       35
2              |        Amy        |       30

And here is what the final result of the query needs to look like:
personKey  |   happy   |   quiet  |   grumpy   |   personName   |   personAge
1          |     1     |    4     |     3      |      Bob       |      35
2          |     3     |    2     |            |      Amy       |      30

I'm sure there's a way to get this query from SQL, but I'm just not experienced enough to write the join and the pivot. Any help is greatly appreciated. Thanks.

I should mention that the ordering of the columns does NOT matter in the final result.

For now I'm just processing the data in R but this is VERY cumbersome and I feel like a simple SQL query with crosstabs would make my life so much easier.

There is an additional module for postgresql that contains a crosstab function https://www.postgresql.org/docs/9.5/static/tablefunc.html — Eelke, Aug 26 '16 at 04:35

score 0 · Answer 1 · answered Aug 07 '18 at 14:33

You can use crosstab function to create a pivot table. Before using these example you need to install the extension:

CREATE EXTENSION tablefunc

You need sort also carefully your data. The manual:

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row.

You need to sort your data with "personKey" and attitudeName in order to get right values into right columns.

After that you can execute this query. :

select ct."personKey",ct.happy,ct.quiet,ct.grumpy,ct."personName",ct."personAge"
from crosstab($$
select t.person_key,
   p.person_name,
   p.person_age,
   a.attitude_name,
   t.response
from main_table t
 join person_table p on p.person_key = t.person_key
 join attitude_table a on a.attitude_key = t.attitude_key
order by 1,
     case t.attitude_key
       when 4 then 1
       when 1 then 2
       else 3
     end
$$,
$$
select t.attitude_name
from attitude_table t
order by case t.attitude_key
       when 4 then 1
       when 1 then 2
       else 3
     end
$$) 
as ct("personKey" INTEGER, "personName" TEXT,"personAge" INTEGER, happy INTEGER, 
quiet INTEGER, grumpy INTEGER)

Postgresql star schema pivot query?

1 Answers1