Как транспонировать несколько столбцов в несколько строк?

Question

[Илья]

Сложно сформулировать, покажу на примере.
В таблице несколько полей с датами и им соответствуют поля со значениями, но мне не нужно, чтобы для каждого типа значений(val1, val2 и тд) были отдельные столбцы в выгрузке, мне нужно, чтобы был один столбец с названием значения и один со значениями, поэтому, я написал запрос:

select val1_date as date, 'val1' as val_name, count(val1) as value
from ... 
group by 1,2
union
select val2_date as date, 'val2' as val_name, count(val2) as value
from ... 
group by 1,2

Получаем соответственно:

А вопрос собственно в том, как это написать нормально, без использования юнион, одним запросом.

Comments

[longclaps]

А что, у оператор UNION уже лишен статуса "нормальный"?

[Илья]

longclaps, ну почему, просто тут, я думаю, есть решение куда оптимальнее и красивее. в примере у меня два селекта соединяются, а на практике у меня их 5 и они больше, но при этом почти одинаковые, отличаются только 2 поля - с датой и со значениями.

[longclaps]

Илья, есть такой класс баз данных - столбцовые, может, они так умеют - я не выяснял. А SQL-базы под транспонирование не заточены.
Всегда, однако, остаётся шанс подумать: а что, если структура таблиц не удовлетворяет твоим потребностям и её можно перестроить? Тогда эта задача - как два пальца об асфальт )

[Илья]

longclaps, вертика и есть как раз колоночная бд)
По поводу перестроить, не подходит, это витрина в КХД, которой пользуюсь не только я, перестраивать её никто не будет) Пока что оставлю юнион, может еще что придёт в голову)

Answer 1

[Константин Цветков]

Это называется PIVOT: hp+vertica+pivot.

Например, календарный отчёт, на MS SQL.

таблица с полями: id, month_id, kpi1, kpi2
чтобы стало: id, kpi1-2020-01-01, kpi1-2020-02-01, kpi2-2020-01-01, kpi2-2020-02-01.

Без PIVOT

SELECT ID, SUM(M1K1), SUM(M2K1), SUM(M1K2), SUM(M2K2) FROM
(SELECT ID, kpi1 AS M1K1, 0 AS M2K1, kpi2 AS M1K2, 0 AS M2K2 FROM Table_K WHERE month_id = 1
UNION
SELECT ID, 0, kpi1, 0, kpi2  FROM Table_K WHERE month_id = 2) AS DailyData
GROUP BY ID

Comments

[Илья]

так и не дошли руки разобраться с пивотом до сих пор, вот вспомнил, и что-то гуглинг по предложенному запросу не очень помог, какие-то костыли, которые я не понимаю как применить...
можете дать простой примерчик, пожалуйста?)
вот, например, у меня есть таблица с полями: id, month_id, kpi1, kpi2.
Ну, соответственно, идентификатор клиента, месяца в формате '2020-01-01' и т.д., и в kpi некие значения. таким образом, если у нас данные скажем за 2 месяца, по каждому id будет две строки. а я хотел бы перенести месяц в столбцы, чтобы стало: id, kpi1-2020-01-01, kpi1-2020-02-01, kpi2-2020-01-01, kpi2-2020-02-01.

[Константин Цветков]

Илья, Если вам не нужен PIVOT, используйте UNION и GROUP по нему.