PostgreSQL的行转列函数使用一例

create table t(day date,equipment varchar(20),output integer);
insert into t values('2010-04-01','DAT501',100);
insert into t values('2010-04-01','DAT502',120);
insert into t values('2010-04-01','DAT503',130);
insert into t values('2010-04-02','DAT501',110);
insert into t values('2010-04-02','DAT502',105); 
insert into t values('2010-04-03','DAT503',125);
insert into t values('2010-04-04','DAT501',100);
insert into t values('2010-04-04','DAT503',200);
--想得到如下结果
    day     | dat501 | dat502 | dat503 
------------+--------+--------+--------
 2010-04-01 |    100 |    120 |    130
 2010-04-02 |    110 |    105 |       
 2010-04-03 |        |        |    125
 2010-04-04 |    100 |        |    200     
(4 rows)

--但是直接使用crosstab会导致第3,4行不准确，也就是说中间项为Null就会不准
test=# SELECT * FROM crosstab('select day, equipment, output from t order by 1,2')  AS t(day date, DAT501 integer, DAT502 integer,DAT503 integer);
    day     | dat501 | dat502 | dat503 
------------+--------+--------+--------
 2010-04-01 |    100 |    120 |    130
 2010-04-02 |    110 |    105 |       
 2010-04-03 |    125 |        |       
 2010-04-04 |    100 |    200 |       
(4 rows)

test=# SELECT * FROM crosstab('select day, equipment, output from t order by 1,2',$$values('DAT501'::text),('DAT502'::text),('DAT503'::text)$$)  AS t(day date, DAT501 integer, DAT502 integer,DAT503 integer);
    day     | dat501 | dat502 | dat503 
------------+--------+--------+--------
 2010-04-01 |    100 |    120 |    130
 2010-04-02 |    110 |    105 |       
 2010-04-03 |        |        |    125
 2010-04-04 |    100 |        |    200
(4 rows)
--其他的写法,本质都一样
test=# SELECT * FROM crosstab('select day, equipment, output from t order by 1','select distinct equipment  from t order by 1')  AS t(day date, DAT501 integer, DAT502 integer,DAT503 integer);
    day     | dat501 | dat502 | dat503 
------------+--------+--------+--------
 2010-04-01 |    100 |    120 |    130
 2010-04-02 |    110 |    105 |       
 2010-04-03 |        |        |    125
 2010-04-04 |    100 |        |    200
(4 rows)

crosstab(text source_sql, text category_sql)