Pivot table in MySQL or Postgres

Question

How do we pivot from

to

Reverse of that is shown at :http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

Answer 1

Example data:

create table test (
    custname text, 
    computer numeric, 
    monitor numeric, 
    software numeric);

insert into test values
('Alison', 345.89, 123.45, 78.78),
('Jason', 435.34, 158.23, 243.54);

Query:

select 
    custname "Customer", 
    unnest(array['Computer', 'Monitor', 'Software']) "Item type",
    unnest(array[computer, monitor, software]) "Amount"
from test;

 Customer | Item type | Amount 
----------+-----------+--------
 Alison   | Computer  | 345.89
 Alison   | Monitor   | 123.45
 Alison   | Software  |  78.78
 Jason    | Computer  | 435.34
 Jason    | Monitor   | 158.23
 Jason    | Software  | 243.54
(6 rows)

---

If unnest() is not available you can use union:

select custname "Customer", 'Computer' "Item type", computer "Amount" from test
union select custname, 'Monitor', monitor from test
union select custname, 'Sofware', software from test
order by 1, 2;