在 PostgreSQL 中使用行值作为列 [英] Use row values as columns in PostgreSQL

问题描述

我有以下 brands 表，其中包含每个 month 的 total 销售额，这是先前查询的结果:

I have the following brands table with total sales per month as a result of a previous query:

 id  |   date   | total
-----+----------+------
 123 | Apr-2012 | 100
 123 | Mar-2012 | 150
 123 | Jan-2012 | 500
 987 | Apr-2012 | 5
 987 | Mar-2012 | 0.10
 987 | Feb-2012 | 8

我希望实现以下目标:

 id  | Apr-2012 | Mar-2012 | Feb-2012 | Jan-2012
 123 | 100      | 150      | 0        | 500
 987 | 5        | 0.10     | 8        | 0

我如何使用 date 值作为列，并能够用 0 总数填充缺失的日期?

How do I use the date values as columns and be able to fill in missing dates with 0 totals?

推荐答案

A crosstab() 查询示例如下所示:

A crosstab() query for your example would look like this:

要为结果 NULL 值填写 0(在评论中请求)，请使用 COALESCE():

To fill in 0 for resulting NULL values (request in comment), use COALESCE():

SELECT brand_id
     , COALESCE(jan, 0) AS "Jan-2012"
     , COALESCE(feb, 0) AS "Feb-2012"
     , COALESCE(mar, 0) AS "Mar-2012"
     , COALESCE(apr, 0) AS "Apr-2012"
FROM crosstab(
       'SELECT brand_id, month, total
        FROM   brands
        ORDER  BY 1'

       ,$$VALUES ('Jan-2012'::text), ('Feb-2012'), ('Mar-2012'), ('Apr-2012')$$
 ) AS ct (
   brand_id int
 , jan numeric    -- use actual data type!
 , feb numeric
 , mar numeric
 , apr numeric);

此相关答案中的详细说明和链接:
PostgreSQL 交叉表查询

Detailed explanation and links in this related answer:
PostgreSQL Crosstab Query

除此之外:不使用保留字日期"作为列名，您也不​​应该使用，即使 Postgres 允许这样做.

Aside: not using the reserved word "date" as column name and you shouldn't either, even if Postgres allows it.

这篇关于在 PostgreSQL 中使用行值作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！