Collapse rows into one column

问题:

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.

回答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.

回答2:

If your SQL implementation lacks transpose / crosstab functionality, you could also unroll / denormalise the EAV by:

SET search_path='tnp';

-- create some data ...
CREATE TABLE value
        ( id INTEGER NOT NULL
        , attribute varchar
        , value varchar
        , PRIMARY KEY (id,attribute)
        );
INSERT INTO value(id,attribute,value) VALUES
 (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' )
        ;

SELECT v.id
        , l1.value AS lab1
        , l2.value AS lab2
        , l3.value AS lab3
        , l4.value AS lab4
        , l5.value AS lab5
        , l6.value AS lab6
FROM value v
LEFT JOIN value l1 ON l1.id = v.id AND l1.attribute = 'Lab1'
LEFT JOIN value l2 ON l2.id = v.id AND l2.attribute = 'Lab2'
LEFT JOIN value l3 ON l3.id = v.id AND l3.attribute = 'Lab3'
LEFT JOIN value l4 ON l4.id = v.id AND l4.attribute = 'Lab4'
LEFT JOIN value l5 ON l5.id = v.id AND l5.attribute = 'Lab5'
LEFT JOIN value l6 ON l6.id = v.id AND l6.attribute = 'Lab6'
        ;

I agree: this is not very elegant. But it works.