Как получить все записи в отдельные столбцы с группировкой по ключу?

Question

[Aljo]

Здравствуйте!
Есть таблица product_parameters с тремя столбцами (product_id, param_key, value), в которой хранятся значения параметров товаров. Т.е. для одного товара может быть несколько записей, но при этом только одна запись для товара по какому-либо параметру.
Например

11 | param1 | крупа
11 | param2 | развес
11 | param3 | 45р/кг
44 | param1 | напиток
44 | param3 | 24р/шт
52 | param1 | быт.химия
52 | param3 | 112р/шт

Так вот, как сделать запрос, чтобы одна строка представляла записи по одному товару и значение каждого параметра было в отдельном столбце, название которого - ключ параметра? Т.е. примерно следующее

product_id | param1 | param2 | param3
11 | крупа | развес | 45р/кг
33 | фрукт | упаковка| 64р/шт

Мне нужно получить только параметры с ключами от param1 до param5.
Пытался через case when и group by, но что-то не то выходит.

Comments

[Сергей П]

Ну так покажите ваши попытки, а мы поправим. Сейчас ваш вопрос выглядит как задание, а это не соответствует тематике ресурса.
Если вы сюда пришли, чтобы вам построили запрос, а не чтобы чему-то научиться, то это не по адресу.
С таким надо к фрилансерам идти.

[Aljo]

Сергей Паньков, я в курсе правил сервиса и не прошу конкретно составлять мне запрос. Можно ведь просто указать, что можно использовать и в сторону чего мне копать. Я ведь предоставил данные, написал, что пробовал. А запрос я сам составлю, просто прошу дать направление.
Вот, мой последний вариант перед вопросом на тостере:

select
    product_id,
    case WHEN param_key = 'param1' then value end param1,
    case WHEN param_key = 'param2' then value end param2,
    case WHEN param_key = 'param3' then value end param3,
    case WHEN param_key = 'param4'  then value end param4,
    case WHEN param_key = 'param5'  then value end param5
from product_parameters
group by product_id

[Aljo]

Сергей Паньков, плюс ко всему я могу это сделать с 5 джойнами, но подумал, что вдруг есть запрос поэлегантнее, поэтому и спросил на сервисе

[Сергей П]

Aljo, 5 left djoin'ов (по одному на каждый параметр) - тоже норм вариант, кстати.

Answer 1

[Aljo]

Получилось сделать так. Не знаю, почему не работает без SUM

SELECT
    product_id,
    SUM(CASE WHEN `param_key ` = 'param1' then `value` END) param1,
    SUM(CASE WHEN `param_key ` = 'param2' THEN `value` END) param2,
    SUM(CASE WHEN `param_key ` = 'param3' THEN `value` END) param3,
    SUM(CASE WHEN `param_key ` = 'param4'  THEN `value` END) param4,
    SUM(CASE WHEN `param_key ` = 'param5'  THEN `value` END) param5
FROM product_parameters
GROUP BY product_id

Comments

[Сергей П]

Нормальный вариант. Но лучше max вместо sum.
Без агрегаторной функии нельзя, потому что у вас группировка.
При группировке столбцы, которые не указаны в перечне group by нужно агрегировать.
Я бы, честно сказать, делал сабселектами.
Как я писал в своем ответе:

SELECT
  p.id
  ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm1') AS param1    
  ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm2') AS param2    
  ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm3') AS param3    
  ,(SELECT value FROM product_parameters pp WHERE pp.product_id = p.id AND pp.param_key = 'parm4') AS param4    
FROM products p

Такая схема будет упоминать продукты, к которым нет ни одного параметра, или они все "по умолчанию".
Если у вас есть индекс по паре полей (product_id, param_key), то такие сабселекты будут делаться за O(1) и в целом все будет быстро.
Но ваш вариант тоже норм, если для всех продуктов объявлен хотя бы один параметр.

Answer 2

[Сергей П]

Транспонирование таблиц в SQL не предусмотрено в большинстве БД.
Если набор параметров динамический, то так не получится, потому что не ясно сколько получится столбцов в итоге, а динамически их делать нельзя.
Можно собрать JSON с атрибутами, но это тоже такое себе.
Обычно такое транспонирование делают в коде на бэке, а не в SQL.
Между тем вы не написали даже какая у вас БД и на чем делаете бэкенд.

На постгресе оптимально было бы сделать джойн, группировку, а при агрегации собирать массив key-value, или JSON, тогда у вас будет два столбца: id, params. Второй столбец будет содержать json'ы.

Ещё один способ можно применить при более-менее статичном наборе возможных параметров. Отдельным запросом получаете все возможные уникальные имена параметров в алфавитном порядке и в том же порядке джойните слева (left join) соответствующие значения. При агрегации вы просто собираете csv-строку с строковыми значениями параметров, а для тех параметров, которых нет, будет пустая строка или null.
Можно собирать не строку с разделителями, а массив строк, или json-список.

Comments

[Aljo]

Если набор параметров динамический, то так не получится, потому что не ясно сколько получится столбцов в итоге, а динамически их делать нельзя.

Вам стоило прочитать мой вопрос, я ведь написал, что мне нужны конкретные параметры с ключами param1 - param5.

[Vitsliputsli]

Aljo, смотрите pivot для вашей субд

[Aljo]

Vitsliputsli, пробовал, вообще не получилось даже близко

[Vitsliputsli]

Aljo, для транспонирования матрицы используется pivot, он может по-разному называться в СУБД, или вообще отсутствовать. При отсутствии, можно его делать через join.
Но, если не получилось, ("что не получилось?" - "ничего не получилось!"), значит для вас эта задача не решаема.