CODEWITHSUNDEEP
sqlpostgresql
Some programmer dude
Some programmer dude

Reputation: 409176

SQL to get result from one query as columns to another query

Lets say I have these two tables:

CREATE TABLE nodes
(
    id      INTEGER PRIMARY KEY,
name    TEXT
);

CREATE TABLE events
(
    id      INTEGER PRIMARY KEY,
    node    INTEGER REFERENCES nodes,
    etime   TIMESTAMP,
    value   TEXT
);

Is there a query to get the values from the nodes table as columns to the events table?

For a simple example I would like something like this:

 node1 |  node2 |  node3
-------+--------+--------
event1 |        |
       | event2 |
       |        | event3

Is this possible?

What I really would like, would be an SQL query that could output something like this:

       etime        |  node1  |  node2  |  node3
--------------------+---------+---------+--------
2011-04-26 13:12:23 | Event 1 |         | Event 2
2011-04-26 15:52:43 |         | Event 3 |        
2011-04-26 21:35:12 | Event 4 | Event 5 | Event 6

Where node1 to node3 comes from the node table, and the timestamps and Event texts comes from the events table.

Upvotes: 4

Views: 1128

Answers (2)

Andriy M
Andriy M

Reputation: 77677

Use grouping and aggregating on CASE expressions:

SELECT
  e.etime,
  MIN(CASE n.name WHEN 'node1' THEN e.value END) AS node1,
  MIN(CASE n.name WHEN 'node2' THEN e.value END) AS node2,
  MIN(CASE n.name WHEN 'node3' THEN e.value END) AS node3
FROM events e
  INNER JOIN nodes n ON e.node = n.id
GROUP BY

Upvotes: 1

Victor Sergienko
Victor Sergienko

Reputation: 13475

This is called crosstab or pivot query, and there is no SQL-compliant way to generate one.

Quick googling says there's a contrib module for PostgreSQL (looks like in core now).

Another pretty interesting blog post here (first in google) - though it's not fully crosstab, as it has fixed number of columns.

Upvotes: 2

Related Questions