Collapse rows into one column

Question

I have a table with the following layout:

 ID   Label   Value
 --   -----   -----
 1    Lab1    Value1-1
 1    Lab2    Value1-2
 1    Lab3    Value1-3
 1    Lab4    Value1-4
 1    Lab5    Value1-5
 1    Lab6    Value1-6
 2    Lab1    Value2-1
 2    Lab2    Value2-2
 2    Lab3    Value2-3
 2    Lab4    Value2-4
 2    Lab5    Value2-5
 2    Lab6    Value2-6
 ...

I'd like to convert the table to be laid out as follows:

 ID   Lab1      Lab2       Lab3       Lab4       Lab5       Lab6
 1    Value1-1  Value1-2   Value1-3   Value1-4   Value1-5   Value1-6
 2    Value2-1  Value2-2   Value2-3   Value2-4   Value2-5   Value2-6
 ...

I'm using SQL in PostgreSQL. Is there an easy (and memory efficient) way to do this? I've seen some posts that mention using pivots, but I'm not sure if that would work and the descriptions I saw appeared to be specific to Oracle.

Answer 1

First install the extension tablefunc, if you haven't already. Needs to be done once per database.

CREATE EXTENSION tablefunc;

You need PostgreSQL 9.1 for CREATE EXTENSION. In older versions you have to run the install script from the shell with a command like:

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

More info for Postgres 9.0 in the fine manual.

Then you can use a query like this one:

SELECT *
FROM   crosstab (
    'SELECT id
           ,label
           ,value
     FROM   t
     ORDER  BY 1, 2',

    'SELECT DISTINCT label
     FROM   t
     ORDER  BY 1')
AS tbl (
 id   int
,lab1 text
,lab2 text
,lab3 text
,lab4 text
,lab5 text
,lab6 text
);

Returns exactly what you asked for.
You can also create a function for that. I added more information in this closely related answer.