MySQL: отображать одну строку в виде столбца (просто, через SELECT)

#mysql #sql #unpivot

#mysql #sql #отключить

Вопрос:

Есть ли простой способ ВЫБРАТЬ одну строку в MySQL и перенести ее так, чтобы заголовки столбцов были перечислены в левом столбце, а значения перечислены в правом столбце?

Пример:

Вместо этого:

 SELECT * FROM sock_table LIMIT 1;

 --------- -------------- ------------ 
| sock_id | smell_factor | hole_count | 
 --------- -------------- ------------ 
| S000123 | 7.2          | 1          |
 --------- -------------- ------------

… вы могли бы сделать что-то вроде этого:

 SELECT TRANSPOSE(*) FROM sock_table LIMIT 1;

sock_id      S000123
smell_factor     7.2
hole_count         1

Для тех, кто знаком с python — pandas, df.loc[0] (где 0 — позиция индекса) автоматически выполняет эту функцию.

Это было бы для таблицы с десятками заголовков столбцов, поэтому ввод заголовков — это больше хлопот, чем того стоит.

1. Использование UNION запросов для каждого столбца.

2. Нет ничего встроенного, что делало бы это.

3. Просто обновил свой вопрос, чтобы отметить, что ввод имен столбцов приведет к поражению цели. И, возможно, @Barmar прав: отсутствие встроенной функциональности.

4. Если вы не хотите вводить все подзапросы для каждого столбца, вы можете создать хранимую процедуру, которая генерирует динамический SQL из INFORMATION_SCHEMA.COLUMNS таблицы.

Ответ №1:

В MYSQL используйте UNION ALL для отмены:

 (select 'stock_id' col, stock_id val FROM stock_table order by id limit 1)
union all (select 'smell_factor' col, smell_factor val FROM stock_table order by id limit 1)
union all (select 'hole_count' col, hole_count val FROM stock_table order by id limit 1)

Важные примечания:

для этого требуется один подзапрос на столбец
LIMIT without ORDER BY нестабилен; вам нужно детерминированное ORDER BY предложение — я предположил, что столбец id можно использовать для упорядочения записей
Типы данных значений должны быть согласованными — они могут вам понадобиться cast , если это не так

Редактировать

На самом деле, самые последние версии MySQL (8.0.19 или выше) позволяют выразить это с values row() помощью and lateral , что сокращает запрос:

 select x.* 
from (select * from mytable order by id limit 1) t,
lateral (values
    row ('stock_id', t.stock_id), 
    row ('smell_factor', t.smell_factor),
    row ('hole_count', t.hole_count)
) x(col, val)

1. Спасибо за публикацию GMB, но эта примерная таблица в моем вопросе была упрощена, в ней будет значительно больше столбцов.

2. @elPastor: итак, вам нужно больше union all членов (по одному на столбец).

3. Ишь, ок. Похоже, сок не стоит выжимать. В любом случае, еще раз спасибо.

4. @elPastor: альтернативный вариант см. В моей правке, если вы используете MySQL 8.0.14 или выше.

5. Спасибо, но опять же, мне пришлось бы вводить десятки столбцов, я ищу простую функцию, которой, как я предполагаю, не существует.

Ответ №2:

Предполагается sock_table , что он состоит из этих трех столбцов и sock_id является первичным ключом таблицы, чтобы использовать его в ORDER BY предложении. Затем рассмотрите возможность использования этого оператора динамического запроса, чтобы получить непредоставленный результат :

 SET @sql = NULL;
       
SELECT GROUP_CONCAT(
       CONCAT( 
       '( SELECT "',column_name,'" AS col_name, ',column_name,' AS value 
            FROM ',table_name,' ORDER BY sock_id LIMIT 1 ) ' )
       SEPARATOR 'UNION ALL ')
  INTO @sql     
  FROM information_schema.columns c
 WHERE table_name = 'sock_table'
 ORDER BY ordinal_position;      


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

В этом случае нет необходимости указывать имя каждого столбца по отдельности.