从表的行创建两个列 [英] Creating two colums from rows of a table

问题描述

我有此表以及其他一些列：

I have this table along with few other columns:

months    amount
-----------------
 Jan       100
 Feb       200
 Jan       400 

我想用此表创建一个视图，使它像这样：

I want to create a view with this table so that it be like this:

Jan    Feb
--------------------
100    200
400    0

我进行了搜索，并获得了PL / SQL解决方案，仅使用SQL查询就可以做到吗？
谢谢

I searched for it and got PL/SQL solutions cant it be done with only SQL queries ? Thank You

推荐答案

首先请确保您可以使用交叉表

First make sure, you have Crosstab available

CREATE EXTENSION tablefunc;

要获取交叉表，您至少需要三列

To get the crosstable you want you need at least three columns

例如：

name    months    amount
-------------------------
Helga    Jan       100
Helga    Feb       200
Bernd    Jan       400 

然后创建交叉表，如下所示：

Then you create your Crosstable like this:

SELECT * FROM 
   crosstab('SELECT name, month, amount from temp')
 AS ct(name varchar, jan int, feb int);

name       jan       feb
--------------------------
Helga     100        200
Bernd     400

如果您每个月都有一列，请按照以下示例操作在手册

If you are going to have columns for each month, stick to the example as described in the manual

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

这篇关于从表的行创建两个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！