Как в результатах запроса колонку превратить в строку?

Question

[M-I-B]

Есть база MySQL, в которой хранятся данные о работе:

устройство                |         дата-время          |        метраж
-------------------------------------------------------------------------------
1                         |   01/01/2022 05:00:01       |         1,01
2                         |   01/01/2022 05:00:01       |         2,10
3                         |   01/01/2022 05:00:01       |         3,04
4                         |   01/01/2022 05:00:01       |         1,41
5                         |   01/01/2022 05:00:01       |         2,60
1                         |   02/01/2022 01:01:01       |         3,01
2                         |   03/01/2022 03:12:01       |         6,14
3                         |   04/01/2022 05:13:01       |         1,01
4                         |   05/01/2022 03:24:01       |         5,51
5                         |   06/01/2022 02:35:01       |         3,74

Подскажите, можно ли из этого набора получить данные в виде:

дата                  | устр_1   | устр_2  | устр_3   | устр_4   | устр_5 
--------------------------------------------------------------------------------
01/01/2022 05:00:01   | 1,01     | 2,10    |  3,04    |  1,41    | 2,60
02/01/2022 01:01:01   |  3,01    | null    | null     | null     | null     
03/01/2022 03:12:01   | null     | 6,14    | null     | null     | null     
04/01/2022 05:13:01   | null     | null    | 1,01     | null     | null       
...

Comments

[twobomb]

А json какого вида?

[FanatPHP]

twobomb, этот вопрос вообще не про json

[FanatPHP]

Либо самостоятельно гуглить mysql pivot table, либо ждать Akina или Slava Rozhnev которые напишут готовый рабочий пример.
Но чтобы им не лень было, лучше добавить в вопрос не картинку, а дамп с запросами CREATE TABLE и INSERT

[Slava Rozhnev]

FanatPHP, после того комментария я обязан жениться. То есть написать запрос

[Slava Rozhnev]

Mellorn, Ждем вариант от Akina

[Akina]

Slava Rozhnev, ну если окажется, что список устройств динамический - тогда да. А если он статический, то вполне хватит и имеющихся ответов, всё равно ничего лучше условной агрегации не придумать.

Answer 1

[Михаил]

Если известно количество устройств (и соответственно количество колонок в итоговой таблице) - можно. Пример для 4 устройств:

CREATE TABLE data(
  device INT NOT NULL,
  date DATETIME NOT NULL,
  metric NUMERIC(10,2) NOT NULL
);

INSERT INTO data VALUES (1, '2022-01-01 00:00:00', 2.34),
(2, '2022-01-01 00:00:00', 1.23),
(3, '2022-01-01 00:00:00', 3.82), 
(4, '2022-01-01 00:00:00', 0.45),
(1, '2022-01-01 01:00:00', 2.00),
(2, '2022-01-01 01:00:00', 1.82),
(3, '2022-01-01 01:00:00', 3.09);

SELECT date, SUM(d1), SUM(d2), SUM(d3), SUM(d4) FROM (
SELECT date,
CASE WHEN device=1 THEN metric ELSE NULL END AS d1,
CASE WHEN device=2 THEN metric ELSE NULL END AS d2,
CASE WHEN device=3 THEN metric ELSE NULL END AS d3,
CASE WHEN device=4 THEN metric ELSE NULL END AS d4
FROM data
) nested
GROUP BY date;

https://sqlize.online/sql/mysql80/68e677a2fc577e6c...

Comments

[M-I-B]

Михаил, огромное спасибо - это именно то, что нужно!

А можно где-то почитать про оператор nested - что это за зверь такой, не могу уловить его сути: везде упоминается про Nested SQL, а про сам оператор и как его применять - ни слова ...

[Михаил]

M-I-B, это не оператор, это псевдоним таблицы-подзапроса. MySQL требует наличия псевдонима, без него синтаксическая ошибка (хотя он никак не используется). Можете там написать любой (tmp, например). Или вообще сразу агрегировать функциями SUM, без промежуточного подзапроса.

Answer 2

[Slava Rozhnev]

А вот промер для пяти ;)

select 
	_date,
	sum(case when _ustr = 1 then _metr else 0 end) _ustr_1,
	sum(case when _ustr = 2 then _metr else 0 end) _ustr_3,
	sum(case when _ustr = 3 then _metr else 0 end) _ustr_3,
	sum(case when _ustr = 4 then _metr else 0 end) _ustr_4,
	sum(case when _ustr = 5 then _metr else 0 end) _ustr_5
from tbl
group by _date;

SQL online

Comments

[Михаил]

Автор вопроса хочет NULL в ячейках где нет данных, а у вас получается 0.

[Slava Rozhnev]

Михаил, https://sqlize.online/sql/mysql80/3226c41a970e7e05...

[Slava Rozhnev]

Михаил, хочет он. Еще не расписались а он уже хочет :)

[M-I-B]

Slava Rozhnev, огромное спасибо!

"вот жеж блин" - когда ответ есть перед глазами, и вопрос очень простым кажется ...

[Михаил]

M-I-B, на самом деле нифига этот вопрос не простой. Я в свое время с аналогичным вопросом ходил за консультацией к нашему SQL-гуру. Это один из тех случаев, когда решение вроде и несложное, но найти его самостоятельно - тот еще ребус.