Запрос для перевода строк в столбцы

В некоторых бизнес-приложениях поступают необработанные данные, я их нормализую и обрабатываю и сохраняю их для целей отчетности.

Например, необработанные данные:

Transaction (transaction ID, employee 1 ID, employee 2 ID, employee 3 ID)

нормализуется в

Transaction(transaction ID)

TransactionEmployee (transaction ID, employee ID)

Когда дело доходит до требований к отчетности, сотрудники должны появляться в одной строке - например TransactionReport(transaction ID, some details, employee 1 ID, employee 2 ID, employee 3 ID)

Моим решением было использовать язык программирования приложений для цикла TransactionEmployee и построить инструкцию INSERT, чтобы поместить данные отчета в другую таблицу - каждая транзакция с 3 идентификаторами сотрудников.

Но мне было бы комфортно это делать в SQL.

Это возможно с помощью SQL?

1 ответ

Один из способов, которым вы могли бы это сделать, - использовать пользовательские переменные для создания номера строки для каждого сотрудника на транзакцию, тогда вы можете преобразовать строки данных в столбец, применив агрегированную функцию с выражением CASE:

select transactionid,
 max(case when row = 1 then employeeid end) employee1,
 max(case when row = 2 then employeeid end) employee2,
 max(case when row = 3 then employeeid end) employee3
from
(
 select t.transactionid,
 e.employeeid,
 @row:=case when @prev = t.transactionid then @row else 0 end +1 row,
 @prev:=t.transactionid
 from transaction t
 left join transactionemployee e
 on t.transactionid = e.transactionid
 cross join (select @row:=0, @prev = 0) c
 order by t.transactionid, e.employeeid
) d
group by transactionid
order by transactionid;

См. SQL Fiddle with Demo.

Если вы не хотите использовать пользовательские переменные, вы можете использовать подзапрос, подобный следующему:

select transactionid,
 max(case when row = 1 then employeeid end) employee1,
 max(case when row = 2 then employeeid end) employee2,
 max(case when row = 3 then employeeid end) employee3
from
(
 select t.transactionid,
 e.employeeid,
 (select count(*)
 from transactionemployee te
 where e.transactionid = te.transactionid
 and te.employeeid <= e.employeeid) row
 from transaction t
 left join transactionemployee e
 on t.transactionid = e.transactionid
) d
group by transactionid;

См. SQL Fiddle with Demo

licensed under cc by-sa 3.0 with attribution.