0

I have a postgres table that looks something like this:

cust_id   key   value
---------------------
      1     A      10
      1     B      20
      1     C      30
      2     A       1
      2     B       2
      2     C       3
      2     D       4
      2     E       5
      3     D     100
      3     F     200

I want to write a query that will give me a table grouped by cust_id like so:

cust_id     A     B     C     D     E     F
-------------------------------------------
      1    10    20    30  NULL  NULL  NULL
      2     1     2     3     4     5  NULL
      3  NULL  NULL  NULL   100  NULL   200

I was thinking of using partitions, but I'm having trouble designing the query. Any idea on how to do this?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3685285
  • 6,066
  • 13
  • 54
  • 95
  • This is not exactly a duplicate. I need the column names to be the strings in the 'key' column, not arbitrary 'val1', 'val2', ect... – user3685285 Dec 07 '17 at 21:17
  • The second part of the first answer where they speak about the "Two Parameter crosstab" is what you are after I believe. The person answering says "I wish you had a category column" and here you have exactly that in your `key` column. So ignore the row_number() bit and stick your `key` column in it's place. – JNevill Dec 07 '17 at 22:07
  • Yes, but I would still have to enumerate out the columns. I actually have potentially hundreds of columns. I want the program to generate unique columns automatically. – user3685285 Dec 07 '17 at 22:14
  • There is a comment under that extremely great answer (just read the whole thing for the first time and it's very impressive). Anyway the comment under the answer provides a link to [this question](https://stackoverflow.com/questions/12988575/crosstab-with-a-large-or-undefined-number-of-categories/12989297#12989297) wherein the answer given deals with dynamic pivoting. Unfortunately, like always with dynamic pivots, the answer isn't pretty, but it should get you in the ballpark. – JNevill Dec 08 '17 at 14:32
  • Also... generally it's recommended that you query the data that you need and then allow whatever UI is consuming the data to perform the dynamic pivot. There really isn't any database that does this well. – JNevill Dec 08 '17 at 14:33

0 Answers0