Oracle

Oracle SQL: Transponiere Zeilen in Spalten

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