该扩展包初步使用参考： http://my.oschina.net/Kenyon/blog/54357
一、环境
OS:CentOS 6.3
DB:Postgresql 9.3.0
二、场景
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',110);
insert into t values('2010-04-02',105);
insert into t values('2010-04-03',125);
insert into t values('2010-04-04',100);
insert into t values('2010-04-04',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)
三、解决
crosstab还有一个包含两个输入参数的用法，用这个可以解决上述问题
test=# SELECT * FROM crosstab('select day,2',$$values('DAT501'::text),('DAT502'::text),('DAT503'::text)$$) AS t(day date,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,output from t order by 1','select distinct equipment from t order by 1') AS t(day date,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其实更安全，推荐使用，其基础用法是
crosstab(text source_sql,text category_sql)
参考：
http://www.postgresql.org/docs/9.2/static/tablefunc.html
