mysql 行转列 递归,PostgreSQL的行转列函数使用一例

开发同事说使用postgres的扩展行转列应用时有一个问题，示例如下

该扩展包初步使用参考：

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','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)

三、解决

crosstab还有一个包含两个输入参数的用法，用这个可以解决上述问题

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其实更安全，推荐使用，其基础用法是

crosstab(text source_sql, text category_sql)

参考：

http://www.postgresql.org/docs/9.2/static/tablefunc.html