fauxgt4 fauxgt4 - 3 years ago 398
SQL Question

PostgreSQL one to many pivot with crosstab

I am trying to build a one to many query in

PostgreSQL
that looks at a customer table, a transaction table and the result is a table showing which products the customer has purchased.

table: customers
id
1
2
3
4
5

table: purchases
custid product
1 software 1
1 software 2
3 software 2
3 software 3
4 software 1
5 software 1
5 software 2
5 software 3


The result I want to get is:

custid software1 software2 software3
1 TRUE TRUE NULL
3 NULL TRUE TRUE
4 TRUE NULL NULL
5 TRUE TRUE TRUE


From what I can tell I need to use
crosstab
(since
postgreSQL
doesn't support
pivot
), but I'm not sure on the snytax. I'd also be fine if the output included row:

custid software1 software2 software3
2 NULL NULL NULL


If its easier to do it one way or the other, it doesn't really matter.

Answer Source

Ignoring customers that did not buy anything, because that's a bit shorter and faster:

SELECT * FROM crosstab(
    'SELECT custid, product, true FROM purchases ORDER BY 1, 2'    
  , $$VALUES ('software 1'::text), ('software 2'), ('software 3')$$)
AS ct (custid int, software1 bool, software2 bool, software3 bool);

Details:

One minor difficulty here: you need to add the boolean value in the query, since it's not in the table.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download