sql - Transpose Query Result in Oracle 11g -

i have query returns out result in following form:

 element  reading1  reading2 reading3 --------------------------------------- 1        0.25      1.5      3.5 2        1.3       2.3      5.5 3        4.5       5.5      4.3 ..        ..       ..       ..    n        1.5       2.3      5.5 --------------------------------------- 

i want output in following form:

            1        2          3    ..     n          --------------------------------------- reading1  0.25     1.3        4.5   ..    1.5 reading2  1.5      2.3        5.5   ..    2.3 reading3  3.5      5.5        4.3   ..    5.5 

i.e have transpose table. have tried using oracle pivot following way:

with t ( select  element,reading1 zzz;    ----(1) ) select * t pivot( max(reading1) element in (1,2,3,..n))     ----(2) 

this gives me result reading1,however unable produce result readings correctly. highly appreciated.

thanks in advance,

best regards, kunal

you're close - want combination of unpivot , pivot:

with t (   select 1 element, 1.1 reading1, 1.2 reading2, 1.3 reading3 dual union   select 2 element, 2.1 reading1, 2.2 reading2, 2.3 reading3 dual union   select 3 element, 3.1 reading1, 3.2 reading2, 3.3 reading3 dual  ) select * (   select * t   unpivot (reading_value     reading_name in ("reading1", "reading2", "reading3")     )   pivot(max(reading_value) element in (1,2,3)   ) ) order reading_name 

this query

• converts columns reading1, reading2, reading3 separate rows (the name goes reading_name, value reading_value); gives 1 row per (element,reading_name)
• converts rows 1, 2*, 3 (the values element) columns '1', '2', '3'; gives 1 row per reading_name

update

if list of elements not know until run time (e.g. because user has option of selecting them), need more dynamic approach. here's 1 solution dynamically creates sql statement given list of elements , uses sys_refcursor result set.

-- setup table create table t    select 1 element, 1.1 reading1, 1.2 reading2, 1.3 reading3 dual union   select 2 element, 2.1 reading1, 2.2 reading2, 2.3 reading3 dual union   select 3 element, 3.1 reading1, 3.2 reading2, 3.3 reading3 dual ;   /  declare   l_elements dbms_sql.number_table;    function pivot_it(p_elements in dbms_sql.number_table)      return sys_refcursor       l_sql clob := empty_clob();       l_result sys_refcursor;     begin       l_sql := '         select * (           select * t              unpivot (reading_value               reading_name in ("reading1", "reading2", "reading3")             )           pivot(max(reading_value) element in (';       in 1 .. p_elements.count               loop                   l_sql := l_sql || to_char(p_elements(i)) || ',';                 end loop;       -- remove trailing ','                       l_sql := regexp_replace(l_sql, ',$');                       l_sql := l_sql || ')         )       )';       dbms_output.put_line(l_sql);       open l_result l_sql;       return l_result;   end;       begin   l_elements(1) := 1;   l_elements(2) := 2;   -- uncomment line 3 elements   -- l_elements(3) := 3;   -- return cursor bind variable (to used in host environment)   :p_cursor := pivot_it(l_elements);   end; 

how use cursor returned function depends on environment you're using - in sql/plus can print it, , programming languages' oracle bindings support out-of-the-box.

caveat: while code works data provided, lacks basic error checking. important because dynamic sql possible target sql injection attacks.