MySQL транспонирование

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

Исходные данные

Итак, у нас есть таблица-справочник с именами систем и у нас есть таблица соответствия идентификаторов пользователя и системы.

Step-by-step

1. Самое простое - нужно знать, как вывести "X" в выходную таблицу. С этим все просто:

SELECT 'X' AS col_name

2. Создадим промежуточную таблицу, в которой будут лежать наши данные:

DROP TABLE IF EXISTS tmp_results;


CREATE
TEMPORARY TABLE tmp_results AS
SELECT a.`id`,
       a.`employeeid`,
       s.`name`
FROM `systemaccess` AS a
LEFT OUTER JOIN `system` AS s ON a.systemid = s.id;

3. Нам нужно получить список систем, которые участвуют в выводе, тут тоже все просто:

SELECT DISTINCT `name` AS col_name
FROM tmp_results

4. Теперь нам нужно сделать динамическую трансформацию значений в имена колонок. В этом нам поможет функция GROUP_CONCAT. Основываясь на статье, которую я упомянул в самом начале и первом пункте нашей инструкции сформируем следующий запрос:

SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN name=''',col_name,''' THEN ''',
                             (SELECT 'X' AS col_name),''' END), '''') AS "',col_name,'"')) AS systems
FROM
  ( SELECT DISTINCT `name` AS col_name
   FROM tmp_results ) system_col_names

Этот запрос сформирует следующую строку:

IFNULL(MAX(CASE
               WHEN name='SAP' THEN 'X'
           END), '') AS "SAP",
IFNULL(MAX(CASE
               WHEN name='1C' THEN 'X'
           END), '') AS "1C"

Т.е. при выборе данных БД проверит, есть ли значение с этой системой. Если есть, то она в колонку поставит "X", а графу назовет именем системы.

5. Следующее, что нам потребуется - это динамически сформированный список колонок для выбора основных данных. Тут можно воспользоваться функцией CONCAT.

SELECT CONCAT('
 SELECT employeeid, ',systems,'
 FROM tmp_results
 GROUP BY employeeid
 ORDER BY employeeid') INTO @query

6. Теперь все это подготавливаем и выполняем.

PREPARE STATEMENT
FROM @query;

EXECUTE STATEMENT;

В итоге получаем то, что нам нужно!

Весь код MySQL

DROP TABLE IF EXISTS tmp_results;


CREATE
TEMPORARY TABLE tmp_results AS
SELECT a.`id`,
       a.`employeeid`,
       s.`name`
FROM `systemaccess` AS a
LEFT OUTER JOIN `system` AS s ON a.systemid = s.id;


SELECT CONCAT('
 SELECT employeeid, ',systems,'
 FROM tmp_results
 GROUP BY employeeid
 ORDER BY employeeid') INTO @query
FROM
  ( SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN name=''',col_name,''' THEN ''',
                                 (SELECT 'X' AS col_name),''' END), '''') AS "',col_name,'"')) AS systems
   FROM
     ( SELECT DISTINCT `name` AS col_name
      FROM tmp_results ) system_col_names) AS RESULT ;

PREPARE STATEMENT
FROM @query;

EXECUTE STATEMENT;