mysql - MySQL 或 Postgres 中的数据透视表

Question

我们如何转向

到

反面显示在:http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

Answer 1

示例数据:

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);

查询:

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)

---

如果 unnest() 不可用，您可以使用 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;