Transpose multiple Columns at same

Question

I have this:

Year   Apple   Orange 
1       100       150
2       200       250
3       300       350
2       200       250
1       100       150

I need this:

Fruit       1           2            3
Apple      200         400          300
Orange     300         500          350

I have option A and option B, but it only transposes 1 fruit unless i do an "Union all".

Option A:

select 
     'Apple' as Fruit
     ,MAX(DECODE(year, '1', sum(Apple)) "1"
     ,MAX(DECODE(year, '2', sum(Apple)) "2"
from MyTable

Option B:

select 
     * 
from (
     select 
          Apple
          ,Year 
     from MyTable
     ) 
PIVOT(sum(Apple) for year in ('1', '2', '3'))

Question:

Can U transpose all columns without an "Union"?

Answer 1

This is how you can do it dynamically.

Create statements

CREATE TABLE MyTable
    (Year int, Apple int, Orange int) ;
INSERT ALL 
    INTO MyTable (Year, Apple, Orange)      VALUES (1, 100, 150)
    INTO MyTable (Year, Apple, Orange)       VALUES (2, 200, 250)
    INTO MyTable (Year, Apple, Orange)       VALUES (3, 300, 350)
    INTO MyTable (Year, Apple, Orange)       VALUES (2, 200, 250)
    INTO MyTable (Year, Apple, Orange)       VALUES (1, 100, 150)
SELECT * FROM dual;

Run this in SQL Developer or SQLPlus (I tried in SQL Developer). Or you can encapsulate it in a procedure and can return the result.

SET ServerOutput ON size 100000;
variable rc refcursor;
DECLARE 
v_column_list varchar2 (2000);
v_years varchar2(2000);
BEGIN
    SELECT listagg('"' || column_name || '"', ',') within
    GROUP (ORDER BY column_id)
    INTO v_column_list
    FROM all_tab_columns
    WHERE table_name = 'MYTABLE'
        AND column_name <> 'YEAR';

    SELECT listagg(year, ',') within
    GROUP (ORDER BY year)
    INTO v_years
    FROM (
        SELECT DISTINCT year
        FROM MyTable);
--  dbms_output.put_line(' v_column_list =' || v_column_list);
--  dbms_output.put_line(' v_years =' || v_years);
    OPEN :rc FOR 
    'SELECT * FROM 
   ( SELECT *
        FROM MyTable 
          UNPIVOT ( val for fruit in ( ' || v_column_list || ' ) 
                  )
    ) 
    PIVOT ( sum ( val ) for year in ( ' || v_years || ' ) )';
END;
/

PRINT :rc

Output:

------------------------------------------------------------------------
FRUIT   1                       2                       3                      
------  ---------------------- ---------------------- ---------------------- 
ORANGE  300                     500                     350                 
APPLE   200                     400                     300   

Answer 2

Oracle Setup:

CREATE TABLE table_name ( year, apple, orange ) AS
SELECT 1, 100, 150 FROM DUAL UNION ALL
SELECT 2, 200, 250 FROM DUAL UNION ALL
SELECT 3, 300, 350 FROM DUAL UNION ALL
SELECT 2, 200, 250 FROM DUAL UNION ALL
SELECT 1, 100, 150 FROM DUAL;

Query - Unpivot then pivot:

SELECT *
FROM   (
  SELECT *
  FROM   table_name
  UNPIVOT( value FOR fruit IN ( Apple, Orange ) )
)
PIVOT ( SUM( value ) FOR year IN ( 1, 2, 3 ) );

Output:

FRUIT  1   2   3
------ --- --- ---
ORANGE 300 500 350
APPLE  200 400 300