-- Beispieltabelle
WITH SimpleTable (ParameterName, ParameterValue) AS (
SELECT 'parameter1', 1 FROM dual UNION ALL
SELECT 'parameter2', 2 FROM dual UNION ALL
SELECT 'parameter3', 3 FROM dual UNION ALL
SELECT 'parameter4', 4 FROM dual UNION ALL
SELECT 'parameter5', 5 FROM dual)
-- Aufgabe: Transponiere alle Zeilen zu Spalten
-- Alternative 1: pivot clause
SELECT *
FROM SimpleTable
PIVOT (max(ParameterValue) FOR ParameterName IN ('parameter1' AS parameter1, 'parameter2' AS parameter2, 'parameter3' AS parameter3, 'parameter4' AS parameter4, 'parameter5' AS parameter5))
;
-- Alternative 2: conditional aggregation
SELECT
max(case when ParameterName = 'parameter1' then ParameterValue end) as parameter1,
max(case when ParameterName = 'parameter2' then ParameterValue end) as parameter2,
max(case when ParameterName = 'parameter3' then ParameterValue end) as parameter3,
max(case when ParameterName = 'parameter4' then ParameterValue end) as parameter4,
max(case when ParameterName = 'parameter5' then ParameterValue end) as parameter5
FROM SimpleTable
;
Mit SQL-Mitteln kann man jedoch die Transposition nur durchführen, wenn man die alten Zeilen, bzw. neuen Spalten explizit im SQL-Statement benennt. Will man generisch eine beliebige Anzahl beliebig benannter Zeilen transponieren, braucht man ziemlich viel PL/SQL:
DECLARE
col varchar2(1000);
cols varchar2(1000);
quer varchar2(1000);
parameterNumber NUMBER := 1;
BEGIN
for i in (select distinct ParameterName pn
from SimpleTable)
loop
col := 'max(case when ParameterName = ''parameter' || parameterNumber || ''' then ParameterValue end) as parameter' || parameterNumber;
IF cols IS NULL THEN
cols := col;
ELSE
cols := cols || ',' || col;
END IF;
parameterNumber := parameterNumber + 1;
end loop;
quer := 'SELECT ' || cols || ' FROM SimpleTable';
DBMS_OUTPUT.PUT_LINE(quer);
END;