PostgreSQL row-to-column function example

Development colleagues said that the use of ipvs extension row-to-column application has a problem, the example of the initial use of the extension package reference: www. linuxidc. comLinux2013-10

The developer said there was a problem with the extended row-to-column application using ipvs, for example, the extension package for preliminary use reference: http://www.linuxidc.com/Linux/2013-10

The developer said that there was a problem when using postgres's extended row-to-column application. The example is as follows:

For more information about the extension package, see:

I. Environment
OS: CentOS 6.3
DB: PostgreSQL 9.3.0

Ii. Scenarios

Create table t (day date, equipment varchar (20), output integer );
Insert into t values ('2017-04-01 ', 'dat501', 2010 );
Insert into t values ('2017-04-01 ', 'dat502', 2010 );
Insert into t values ('2017-04-01 ', 'dat503', 2010 );
Insert into t values ('2017-04-02 ', 'dat501', 2010 );
Insert into t values ('2017-04-02 ', 'dat502', 2010 );
Insert into t values ('2017-04-03 ', 'dat503', 2010 );
Insert into t values ('2017-04-04 ', 'dat501', 2010 );
Insert into t values ('2017-04-04 ', 'dat503', 2010 );
-- Expected result:
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)

-- But directly using crosstab will result in inaccurate Row 3 and 4. That is to say, if the intermediate item is Null, it will be inaccurate.
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 |
| 125 |
| 100 | 200 |
(4 rows)

Iii. Solution
Crosstab also has a usage that contains two input parameters. This can solve the above problem.
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) -- Other statements are essentially the same
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)

Iv. Description
Crosstab with two parameters is actually more secure and recommended. Its basic usage is

Crosstab (text source_ SQL, text category_ SQL)

Refer:

Related reading:

PostgreSQL 9.3 materialized view usage

Four tips for using PostgreSQL database date type

PostgreSQL deletes duplicate data rows in a table

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu