1

I have a hits table in PostgreSQL that contains a record for each product found in a certain shop.

It looks like this (simplified):

id shop product
1 shop1 product1
2 shop1 product85
3 shop3 product1
4 shop3 product1
5 shop4 product23
6 shop4 product64
7 shop4 product128

As you can see for shop 3, a certain shop may have more than one hit for a certain product.

I want to query it in a way that I get as the output of the query this simple pivot table (which I will use to create a view), where 1 means, the respective shop has the specific product in stock, and 0 means, it doesn't:

id shop product1 product2 ... product23 ... product64 ... product85 ...
1 shop1 1 0 ... 0 ... 0 ... 1
2 shop3 1 0 ... 0 ... 0 ... 0
3 shop4 0 0 ... 1 ... 1 ... 0

Please note: I do not want to hard-code all the columns, as there are many and the number of products might change.

I would prefer not to use crosstab from the tablefunc extension.

a learner has no name
  • 775
  • 8
  • 16
  • 2
    Not possible. A fundamental restriction of the SQL language is, that the number, names and data types of all columns of a query must be know to the engine _before_ the query is being executed (i.e. when parsing the query). There are two workarounds: create a view dynamically when the number of products changes (and then wait until it fails if you have more than ~1500 products). Or aggregate into a JSON structure, e.g. like [this](https://blog.sql-workbench.eu/post/pivot-alternatives). A pivot table like that is better done in the GUI of your application. –  May 03 '22 at 14:28
  • @a_horse_with_no_name Thanks. I'm trying to figure out if [this answer](https://stackoverflow.com/a/70466824/1687087) from another thread offers a solution. – a learner has no name May 03 '22 at 14:46
  • The "workaround" with a type seems rather pointless if you can create a view dynamically as well. But in any case you'll hit the limit on the number of roughly 1500 columns in a view or query quickly with any real world online shop. –  May 03 '22 at 14:49
  • @a_horse_with_no_name Currently, the number of products is in the low hundreds. I do not expect to exceed 1000 anytime soon, if at all. I could also build different views for different ranges of products. – a learner has no name May 03 '22 at 14:56
  • @a_horse_with_no_name I guess you are right. In the end, we only wanted to use the pivot table as a basis for more specific visualisations in QuickSight, which we now realised we can also achieve in a different way. In most other cases, a JSON structure would probably be most appropriate. – a learner has no name May 04 '22 at 09:25

0 Answers0