0

I have a below table with custom values. Id, productname and value can be anything. I want to read productname, create new column with that productname and put values belong to that productname under it.

Table:

ID ProductName Value
1 product1 111
1 product2 112
2 product1 221
2 product2 222
3 product1 331
1 product3 113

what I want:

ID Product1 Product2 Product3
1 111 112 111
2 221 222
3 331
Ishan Patel
  • 11
  • 1
  • 1
  • Is there the possibility that you could have two records for the same `id`, `product name` combination? If so, how would that be outputted? Also... how many distinct product names are in this table? It feels very much like a dynamic pivot is going to be needed which is going to get ugly. Generally the advice for that route is to pivot in your application that is consuming/displaying this data. – JNevill Mar 07 '22 at 17:25
  • At a glance, this: https://stackoverflow.com/q/39779734/8954291 might help – Jakob Lovern Mar 07 '22 at 17:42
  • @JNevill No, id and product name combination is unique. There could be max 15 to 20 combinations in table. What I want is to sort values per id and product name combination, so I have to first get the output then perform orderby. – Ishan Patel Mar 07 '22 at 18:19
  • Please provide enough code so others can better understand or reproduce the problem. – Community Mar 08 '22 at 09:07

1 Answers1

0

So one thing that SQL can't do is give you an arbitrary number of columns. If you think about executing a query as two steps, the first step sets up the memory for the query by reading how many columns to expect, and the second actually fills it. If your (hypothetical) query were to have an arbitrary number of columns, it wouldn't be able to tell how many columns it needed until runtime.

That being said, there are a few ways to do it if you know ahead of time how many columns you need. In postgresql, you're going to want to use the crosstab() function. Crosstab functions (kinda) like a pivot table in Excel. I'm not entirely familiar with it, so I'm working off this documentation page. Try the following:

SELECT *
FROM crosstab(
    'select Id,ProductName,value
     from base_table
     order by 1',
    'select distinct productName
     from base_table
     order by 1')
as ct(id, Product1, Product2, Product3);

Note: This is only a best-guess. It's likely not perfect for your purposes.

Jakob Lovern
  • 1,301
  • 7
  • 24