0

I have a table view as below: 

(SELECT * FROM session_log_detail)
![Raw Data from table session_log_detail][1]

attr_name     attr_value     timestamp
A                123           1:20:45
B                234           1:20:45
C                567           1:20:45
D                234           1:20:45
A                098           2:58:20
B                478           2:58:20
"                 "              "

What I would like to do is retrieve data like:

A         B          C           D
123      234        567         234
098      478

Where the time stamp value should be equal for each row, If changed need to be on the next line. attr_values and timestamp are random values.

I have Postgres SQL 9.0.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1564173
  • 31
  • 3
  • 7

1 Answers1

1

Tablefunc module

You are looking for "pivot" or "crosstab". If you haven't installed the additional module tablefunc already, install it once per database. In PostgreSQL 9.1+ this has been simplified to:

CREATE EXTENSION tablefunc;

In older versions (including 9.0) you have to run the install script from the shell with a command like:

psql -d dbname -f SHAREDIR/contrib/tablefunc.sql

For version 9.0 under Debian, this would be:

psql -d dbname -f /usr/share/postgresql/9.0/contrib/tablefunc.sql

More info for Postgres 9.0 in the fine manual.

Query

Could look like this:

SELECT * FROM crosstab(
       'SELECT ts, attr_name, attr_value
        FROM   session_log_detail
        ORDER  BY 1,2'

      ,$$VALUES ('A'::text), ('B'), ('C'), ('D')$$)
AS tbl ("ts" text, "A" int, "B" int, "c" int, "D" int);

I named your column ts, since timestamp is a function name in Postgres and should not be used as identifier.

Find more explanation and details in these related answers:
PostgreSQL Crosstab Query
Dynamic alternative to pivot with CASE and GROUP BY

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Permission for running the script failed, "psql: FATAL: Peer authentication failed for user". – user1564173 Apr 01 '13 at 02:38
  • Please suggest alternative, Do PIVOT require require any script installation prior? – user1564173 Apr 01 '13 at 02:39
  • @user1564173: Try running the install script as system user `postgres`: `sudo su postgres`. – Erwin Brandstetter Apr 01 '13 at 02:54
  • Postgres is installed on a customized linux and most of the commands cannot be executed. Is there a way to hide a row if condition is not matched? I want to able to get a near output using SELECT CASE WHEN attr_name = 'user:name' AND attr_name <> '%' THEN attr_value END "User Name" FROM table_name; But the problem with this query is It displays more empty row and are not NULL. – user1564173 Apr 01 '13 at 05:32
  • @user1564173: As explained above, `CREATE EXTENSION` is for Postgres 9.1+. And for most extensions you need superuser privileges to install them. The db-user `postgres` is the default superuser. And for new questions start a new *question* please. – Erwin Brandstetter Apr 01 '13 at 12:36