Transpose Query Result In Oracle 11g

I have a query which returns out result in following Form: Element READING1 READING2 READING3 --------------------------------------- 1 0.25 1.5 3.5 2 1.

Solution 1:

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

with T AS (
  select1as element, 1.1as reading1, 1.2as reading2, 1.3as reading3 from dual union all
  select2as element, 2.1as reading1, 2.2as reading2, 2.3as reading3 from dual union all
  select3as element, 3.1as reading1, 3.2as reading2, 3.3as 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)
  )
)
orderby 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 ASselect1as element, 1.1as reading1, 1.2as reading2, 1.3as reading3 from dual union all
  select2as element, 2.1as reading1, 2.2as reading2, 2.3as reading3 from dual union all
  select3as element, 3.1as reading1, 3.2as reading2, 3.3as 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 in1 .. p_Elements.count
              loop
                  l_SQL := l_SQL || to_char(p_Elements(i)) || ',';endloop;
      -- 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 toget 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.