Транспонирование строк в столбцы обратно с помощью операторов PIVOT/UNPIVOT
19.02.2016

Если необходимо преборазовать набор строк в набор столбцов с помощью SQL подойдет оператор PIVOT введенный в версии Oracle 11g:

SELECT * FROM (
 SELECT 'Иванов' AS fio, 1 grp, 1 res, 1 position FROM dual UNION ALL
 SELECT 'Иванов' AS fio, 2 grp, 1 res, 2 position FROM dual UNION ALL
 SELECT 'Иванов' AS fio, 3 grp, 1 res, 4 position FROM dual UNION ALL
 SELECT 'Петров' AS fio, 1 grp, 4 res, 6 position FROM dual UNION ALL
 SELECT 'Петров' AS fio, 2 grp, 3 res, 3 position FROM dual UNION ALL
 SELECT 'Чирков' AS fio, 3 grp, 1 res, 1 position FROM dual
)
PIVOT (min(position) AS p, MIN(res) AS r FOR grp IN (1 G1, 2 G2, 3 G3))
ORDER BY fio

 

Исходный набор выглядит так:

Результат применения оператора PIVOT:

Обратное преобразование столбцов в строки осуществляется оператором UNPIVOT:

SELECT * FROM (
SELECT * FROM (
 SELECT 'Иванов' AS fio, 1 grp, 1 res, 1 position FROM dual UNION ALL
 SELECT 'Иванов' AS fio, 2 grp, 1 res, 2 position FROM dual UNION ALL
 SELECT 'Иванов' AS fio, 3 grp, 1 res, 4 position FROM dual UNION ALL
 SELECT 'Петров' AS fio, 1 grp, 4 res, 6 position FROM dual UNION ALL
 SELECT 'Петров' AS fio, 2 grp, 3 res, 3 position FROM dual UNION ALL
 SELECT 'Чирков' AS fio, 3 grp, 1 res, 1 position FROM dual
)
PIVOT (min(position) AS p, MIN(res) AS r FOR grp IN (1 G1, 2 G2, 3 G3))
)
UNPIVOT ( (r,p) FOR grp IN ( (G1_R,G1_P) AS 1, (G2_R,G2_P) AS 2, (G3_R,G3_P) AS 3))
ORDER BY fio,grp

release 1.0