瀚高数据库crosstab()函数实现的行转列

环境

系统平台：Microsoft Windows (64-bit) 10
版本：5.6.5

文档用途

本文介绍使用tablefunc扩展中的crosstab函数实现行转列。

详细信息

1、新建扩展

highgo=# create extension tablefunc;
CREATE EXTENSION

2、举例

例一：

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

);



highgo=# 

 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec

------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------

 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000

 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |

例二：

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);

INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');

INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');

INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');

INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');

INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');

INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');

INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');



SELECT * FROM crosstab

(

  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',

  'SELECT DISTINCT attribute FROM cth ORDER BY 1'

)

AS

(

       rowid text,

       rowdt timestamp,

       temperature int4,

       test_result text,

       test_startdate timestamp,

       volts float8

);

highgo=#

 rowid |        rowdt        | temperature | test_result |   test_startdate    | volts

-------+---------------------+-------------+-------------+---------------------+--------

 test1 | 2003-03-01 00:00:00 |          42 | PASS        |                     | 2.6987

 test2 | 2003-03-02 00:00:00 |          53 | FAIL        | 2003-03-01 00:00:00 | 3.1234