8

I have a table:

Table_Name: price_list
---------------------------------------------------
| id | price_type_a | price_type_b | price_type_c |
---------------------------------------------------
| 1  |    1234      |     5678     |     9012     |
| 2  |    3456      |     7890     |     1234     |
| 3  |    5678      |     9012     |     3456     |
---------------------------------------------------

I need a select query in Postgres which gives result like this:

---------------------------
| id | price_type | price |
---------------------------
| 1  |  type_a    | 1234  |
| 1  |  type_b    | 5678  |
| 1  |  type_c    | 9012  |
| 2  |  type_a    | 3456  |
| 2  |  type_b    | 7890  |
| 2  |  type_c    | 1234  |
...

Any help with links to similar examples greatly appreciated.

skybunk
  • 833
  • 2
  • 12
  • 17
  • just `select id,'a', a union select id,'b'b and so on`?.. – Vao Tsun Nov 02 '17 at 13:56
  • Thanks ! that works great.. will it be optimal for large amounts of data? – skybunk Nov 02 '17 at 14:07
  • nothing smarter comes ATM anyway :) – Vao Tsun Nov 02 '17 at 14:10
  • Thats alright. I was thinking Is there a way to do it via pivot or unpivot? because I have a huge amount of data in this table, pivot would be more optimal that way instead of having multiple unions right? – skybunk Nov 02 '17 at 14:17
  • You would have to look into the [`tablefunc`](https://www.postgresql.org/docs/current/static/tablefunc.html), and in particular at the `crosstab` function. – s.m. Nov 02 '17 at 14:21
  • could you give an example snippet for how we can use `crosstab` for this case? – skybunk Nov 02 '17 at 14:31
  • @s.m.: Crosstab is related, but for "pivoting" (see basics here: https://stackoverflow.com/a/11751905/939860). This task is the reverse operation: "unpivoting". – Erwin Brandstetter Nov 02 '17 at 14:34
  • @ErwinBrandstetter & OP: apologies, I got it completely backwards. – s.m. Nov 02 '17 at 14:39

2 Answers2

13

A single SELECT with a LATERAL join to a VALUES expression does the job of "un-pivoting" columns to separate rows:

SELECT p.id, v.*
FROM   price_list p
CROSS  JOIN LATERAL (
   VALUES
      ('type_a', p.price_type_a)
    , ('type_b', p.price_type_b)
    , ('type_c', p.price_type_c)
   ) v (price_type, price);

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    What if I have a list of 50 columns like this with multiple values. Is there a better way? – sanchitkhanna26 Sep 02 '21 at 18:25
  • @sanchitkhanna26: This works for any number of columns. I suggest you start a *new question* with the details of your use case, and of your objective. (What's "better"? Faster? Safer? Shorter? ...) – Erwin Brandstetter Sep 02 '21 at 21:39
0

try smth like:

select id, 'type_a',type_a  from price_list
union all
select id, 'type_b',type_b  from price_list
union all
select id, 'type_c',type_c  from price_list
;

update as a_horse_with_no_name suggests, union is way to select DISTINCT values, for here would be UNION ALL prefered - just in case (I don't know if id is UNIQUE)

Of course if it is UK - there will be no difference

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132