При помощи SQL вы получаете исходные данные, в виде первой таблицы.
Далее работаете только кодом PHP.
Вторая таблица - это у вас несколько матриц, по количеству подразделений.
Одна матрица n*m это:
n - макс. индекс предмета 000n в пределах одного подразделения.
m - макс. индекс видов предметов во всей исходной выборке.
Позиция в матрице i,j:
i - это 000i
j - индекс вида предмета.
Для вывода результата вам нужно получить структуру:
$result =
[
1 => // department_id
[
1 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0001', 2 => '1_2_0001', 3 => '1_3_0001' /* непосредственно данные */],
2 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0002', 2 => '1_2_0002', 3 => '1_3_0002' /* непосредственно данные */],
3 /* 000i индекс */ => [1 /*индекс вида предмета*/ => '1_1_0003', 3 => '1_3_0003' /* непосредственно данные */],
...
],
2 =>
...
];
Вариант 2, динамически в PHP создать запрос:
select t.department_id,
t1.item as type_1,
t2.item as type_2,
...
from t
left join (select t.department_id, t.item from t where t.type_id = 1) t1 on t1.department_id = t.department_id
left join (select t.department_id, t.item from t where t.type_id = 2) t2 on t2.department_id = t.department_id
...
where 1 = 1
and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t1.item, '_', -1) + 0 or t1.item is null)
and (SUBSTRING_INDEX(t.item, '_', -1) + 0 = SUBSTRING_INDEX(t2.item, '_', -1) + 0 or t2.item is null)
...
-- тут нужно получить равенство всех субиндексов 0000i от t.item сравнить со всеми tn.item
order by t.department_id, SUBSTRING_INDEX(t.item, '_', -1) + 0