2

I have a table with primary key as (state+city) and other columns (product1, product2, product3, product4). I am trying to create a new table where each row will correspond to (state+city) & (one of) products + some static_value columns. How do I do it in a PostgreSQL query?

It's probably better to see examples of input and output data that I am looking at. I checked various examples of converting rows to columns in MySQL or SQL Server on this forum but they don't seem to help me.

Example data sheet ('Input Table' and 'Desired Output' tabs)

https://docs.google.com/spreadsheets/d/1qQZhWXB_h80HfaDi6VOqeOs7CVNyW9gwtEd-GRqLHKc/edit#gid=1581374593

Sony Mitto
  • 145
  • 1
  • 1
  • 9
  • 2
    Possible duplicate of [PostgreSQL convert columns to rows? Transpose?](https://stackoverflow.com/questions/14084503/postgresql-convert-columns-to-rows-transpose) – Serg Jun 15 '19 at 08:18
  • 1
    Please **[edit]** your question (by clicking on the [edit] link below it) and add some [sample data](https://meta.stackexchange.com/questions/81852) and the expected output based on that data. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question - do **not** post code or additional information in comments or link to external sites for sample data –  Jun 15 '19 at 09:46

2 Answers2

3

You can use union all selecting one column at a time as below. You can add other column (TextA, TextB, Date...) to each SELECT query as per requirement.

SELECT State,City,'Product1' Product, Product1 Value FROM your_table
UNION ALL
SELECT State,City,'Product2' Product, Product2 Value FROM your_table
UNION ALL
SELECT State,City,'Product3' Product, Product3 Value FROM your_table
UNION ALL
SELECT State,City,'Product4' Product, Product4 Value FROM your_table
mkRabbani
  • 16,295
  • 2
  • 15
  • 24
2

You can cross join with the individual columns turned to rows:

select t.state, t.city, x.product
form the_table t
  cross join lateral ( 
     values (t.product1), (t.product2), (t.product3)
  ) as x(product)

Or bit more compact:

select t.state, t.city, x.product
form the_table t
  cross join unnest(array[t.product1, t.product2, t.product3]) as x(product)