
-- 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;