Свернуть строки в один столбец
У меня есть таблица со следующей компоновкой:
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
...
Я хотел бы преобразовать таблицу, чтобы она была выложена следующим образом:
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
...
Я использую SQL в PostgreSQL. Есть ли простой (и эффективный для памяти) способ сделать это? Я видел некоторые сообщения, в которых упоминалось использование пивотов, но я не уверен, что это сработает, и описания, которые я видел, были специфичными для Oracle.
2 ответа:
Сначала установите расширениеtablefunc , Если вы еще этого не сделали. Это должно быть сделано один раз для каждой базы данных.
CREATE EXTENSION tablefunc;
Вам нужен PostgreSQL 9.1 для
CREATE EXTENSION
. В более старых версиях вы должны запустить сценарий установки из командной консоли с помощью команды типа:psql -d dbname -f SHAREDIR/contrib/tablefunc.sql
Более подробная информация дляPostgres 9.0 в прекрасном руководстве .
Тогда вы можете использовать такой запрос:
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 );
Возвращает именно то, что вы просили.
Вы также можете создать функцию для этого. Я добавлена дополнительная информация в этомтесно связанном ответе .
Если вашей реализации SQL не хватает функциональности транспонирования / кросс-таблицы, вы также можете развернуть / денормализовать EAV:
Я согласен: это не очень элегантно. Но это работает.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' ;