Transpose Query Result in Oracle 11g

Question

I have a query which 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 I have to transpose the table. I have tried using Oracle Pivot with following way:

WITH T AS (
SELECT  Element,READING1 from ZZZ;    ----(1)
)
SELECT * FROM T
PIVOT( MAX(READING1) FOR ELEMENT IN (1,2,3,..n))     ----(2)

This gives me result only for READING1,however I am unable to produce result for all readings correctly. Any help would be highly appreciated.

Thanks in Advance,

Best Regards, Kunal

Answer 1

You're close - what you want is a combination of UNPIVOT and PIVOT:

with T AS (
  select 1 as element, 1.1 as reading1, 1.2 as reading2, 1.3 as reading3 from dual union all
  select 2 as element, 2.1 as reading1, 2.2 as reading2, 2.3 as reading3 from dual union all
  select 3 as element, 3.1 as reading1, 3.2 as reading2, 3.3 as reading3 from dual 
)
select * from (
  select * from t
  unpivot (reading_value
    for reading_name in ("READING1", "READING2", "READING3")
    )
  pivot(max(reading_value) for element in (1,2,3)
  )
)
order by reading_name

This query

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

UPDATE

If the list of elements is not know until run time (e.g. because the user has the option of selecting them), you need a more dynamic approach. Here's one solution that dynamically creates a SQL statement for the given list of elements and uses a sys_refcursor for the result set.

-- setup table
create table T AS 
  select 1 as element, 1.1 as reading1, 1.2 as reading2, 1.3 as reading3 from dual union all
  select 2 as element, 2.1 as reading1, 2.2 as reading2, 2.3 as reading3 from dual union all
  select 3 as element, 3.1 as reading1, 3.2 as reading2, 3.3 as reading3 from dual ;  
/

declare
  l_Elements dbms_sql.Number_Table;

  function pivot_it(p_Elements in dbms_sql.Number_Table) 
    return sys_refcursor is
      l_SQL CLOB := empty_clob();
      l_Result sys_refcursor;
    begin
      l_SQL := '
        select * from (
          select * from t 
            unpivot (reading_value
              for reading_name in ("READING1", "READING2", "READING3")
            )
          pivot(max(reading_value) for element in (';
      for i 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 for l_SQL;
      return l_Result;
  end;      
begin
  l_Elements(1) := 1;
  l_Elements(2) := 2;
  -- uncomment this line to get all 3 elements
  -- l_Elements(3) := 3;
  -- return the cursor into a bind variable (to be used in the host environment)
  :p_Cursor := pivot_it(l_Elements);  
end;

How you use the cursor returned from this function depends on the environment you're using - in SQL/Plus you can just print it, and most programming languages' Oracle bindings support it out-of-the-box.

CAVEAT: While this code works for the data provided, it lacks even basic error checking. This is especially important because dynamic SQL is always a possible target for SQL injection attacks.