PostgreSQL crosstab with dynamic column names and multiple input columns

Question

Problem

I've got a PostgreSQL 9.6 database with a table designed according to an EAV model with different types of values. An example excerpt looks like this:

 name |arrivalTime | boolValue | intValue | floatValue | stringValue
------+------------+-----------+----------+------------+------------
 a1   |  10:00:00  |   true    |          |            |
 c3   |  10:00:00  |           |   12     |            |
 d4   |  10:00:00  |           |          |            | hello
 e5   |  15:00:00  |           |          |    45.67   |
 c3   |  15:00:00  |           |   45     |            |
 b2   |  20:00:00  |           |          |    4.567   |
 a1   |  20:00:00  |   false   |          |            |
 d4   |  22:00:00  |           |          |            |  bye
 b2   |  22:00:00  |           |          |    12.34   |

Empty cells represent null values in the database.

Now I would like to get a Pivot table, were the new columns are the arrivalTime and the content of name. For the example from above it should look like this:

arrivalTime |  a1   |  b2   |  c3   |  d4   |  e5
------------+-------+-------+-------+-------+-------
  10:00:00  | true  |       |  12   | hello |
  15:00:00  |       |       |  45   |       | 45.67
  20:00:00  | false | 4.567 |       |       |
  22:00:00  |       | 12.34 |       |  bye  |

As an input to query for retrieving this result I get an pattern matching the names and a start and end time specifying the range of arrivalTime.

Properties of the original table:

The entries in the name column are volatile, i.e. new names come in and old names vanish regularly.
Each combination of name and arrivalTime is unique.
Each name and arrivalTime combination has exactly one entry in one of the value columns.

Ideas

I already gave it some considerations:

I figured so much, that the crosstab function should be used
Since the columns are dynamic, two queries will be needed as explained in Dynamically generate columns in PostgreSQL or Execute a dynamic crosstab query.
Using the format() function to generate the first query is probably a good idea.

Example Table

Here is the SQL Code to create the example table:

CREATE TABLE IF NOT EXISTS playTable (
  name TEXT NOT NULL,
  arrivalTime TIME NOT NULL,
  floatValue REAL NULL,
  intValue INT NULL,
  boolValue BOOLEAN NULL,
  stringValue TEXT NULL,
  PRIMARY KEY (name, arrivalTime),
  CONSTRAINT single_value CHECK(
    (boolValue IS NOT NULL)::INT + 
    (intValue IS NOT NULL)::INT + 
    (floatValue IS NOT NULL)::INT +
    (stringValue IS NOT NULL)::INT = 1
  )
);

And to insert the values:

INSERT INTO playTable ( name, arrivalTime, boolValue ) VALUES ( 'a1', '10:00:00', true );
INSERT INTO playTable ( name, arrivalTime, intValue ) VALUES ( 'c3', '10:00:00', 12 );
INSERT INTO playTable ( name, arrivalTime, stringValue ) VALUES ( 'd4', '10:00:00', 'hello' );
INSERT INTO playTable ( name, arrivalTime, floatValue ) VALUES ( 'e5', '15:00:00', 45.67 );
INSERT INTO playTable ( name, arrivalTime, intValue ) VALUES ( 'c3', '15:00:00', 45 );
INSERT INTO playTable ( name, arrivalTime, floatValue ) VALUES ( 'b2', '20:00:00', 4.567 );
INSERT INTO playTable ( name, arrivalTime, boolValue ) VALUES ( 'a1', '20:00:00', false );
INSERT INTO playTable ( name, arrivalTime, stringValue ) VALUES ( 'd4', '22:00:00', 'bye' );
INSERT INTO playTable ( name, arrivalTime, floatValue ) VALUES ( 'b2', '22:00:00', 12.34 );

Pivot table, non dynamic

klin provided the starting point of the solution, I guess:

SELECT *
FROM crosstab(
    $ct$
        SELECT 
            arrivalTime, name, concat(boolValue, intValue, floatValue, stringValue)
        FROM playTable
        ORDER BY 1, 2
    $ct$,
    $ct$
        SELECT DISTINCT name 
        FROM playTable
        ORDER BY 1
    $ct$) 
AS ct("arrivalTime" time, "a1" BOOLEAN, "b2" REAL, "c3" INT, "d4" TEXT, "e5" REAL);

What is missing from this solution is the dynamic aspect. As an input, a LIKE pattern for name is supplied and the range (i. e. min and max value) of arrivalTime. This makes the argument of as ct(...) dynamic.

klin · Accepted Answer · 2017-05-09 14:39:46Z

1

Use coalesce() for four last columns. You have to cast the columns to text to do this:

select *
from crosstab(
    $ct$
        select 
            arrivaltime, name, 
            coalesce(boolvalue::text, intvalue::text, floatvalue::text, stringvalue)
        from my_table
        order by 1, 2
    $ct$,
    $ct$
        select distinct name 
        from my_table
        order by 1
    $ct$) 
as ct("arrivalTime" time, "a1" text, "b2" text, "c3" text, "d4" text, "e5" text);

 arrivalTime |  a1   |  b2   | c3 |  d4   |  e5   
-------------+-------+-------+----+-------+-------
 10:00:00    | true  |       | 12 | hello | 
 15:00:00    |       |       | 45 |       | 45.67
 20:00:00    | false | 4.567 |    |       | 
 22:00:00    |       | 12.34 |    | bye   | 
(4 rows)

I have used arrivalTime time because of the format of the example data, change it to timestamp.

answered May 9, 2017 at 14:39

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user711270 Over a year ago

I played around with solution and extended my question a bit. I changed your code from using coalesce to concat. This works fine and important hurdles are taken, but I'm not at the finish yet: Still missing is the ability to put in the pattern specifying the name and the arrivalTime range. This makes the argument of as ct(...) dynamic.

Stan Brajewski Over a year ago

user711270 You need any kind of programming languge, that will generate SQL queries for you, taking care of input patterns. If you are in SQL only use plpgsql to create such dynamic queries.

Collectives™ on Stack Overflow

PostgreSQL crosstab with dynamic column names and multiple input columns

Problem

Ideas

Example Table

Pivot table, non dynamic

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

Ideas

Example Table

Pivot table, non dynamic

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related