将行折叠成一列 [英] 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.
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.
这篇关于将行折叠成一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!