Oracle: any way to transform data this way?

Question

Is there any way to map the first table to the second table with an SQL query or, if too complicated, a PL/SQL block?

Original

--------------------------------------
|  col1   | col2   | col3   | col4   |
--------------------------------------
|  key    | case 1 | case 2 | case 3 |
|  value1 | v1c1   | v1c2   | v1c3   |
|  value2 | v2c1   | v2c2   | v2c3   |
--------------------------------------

Target

-----------------------------
| key     | case   | result |
-----------------------------
| value1  | case 1 | v1c1   |
| value1  | case 2 | v1c2   |
| value1  | case 3 | v1c3   |
| value2  | case 1 | v2c1   |
| value2  | case 2 | v2c2   |
| value2  | case 3 | v2c3   |
-----------------------------

The original table can have a variable number of columns, and 'key' is a hardcoded string and is always in column 1 of the original table. No other row has “key” in column 1, so this row is a unique pivot.

Thank you

Answer 1

If dynamic sql is allowed, then it is possible to have all your requirements fullfilled using one query:

SELECT col1 as "key"
       ,extractvalue(dbms_xmlgen.getXMLType('select "' || tc.Column_Name ||
                                           '" as v from Original where col1 = ''key''')
                   ,'/ROWSET/ROW/V') "case"
       ,extractvalue(dbms_xmlgen.getXMLType('select "' || tc.Column_Name ||
                                           '" as v from Original where col1 = ''' ||
                                           replace(col1, '''', '''''') || '''')
                   ,'/ROWSET/ROW/V') "result"
FROM   Original
      ,(SELECT Column_Name
        FROM   All_Tab_Columns tc
        WHERE  tc.Owner = 'YOURSCHEMA'
        and    tc.Table_Name = 'ORIGINAL'
        and    Column_Name != 'COL1'
        ORDER  BY tc.COLUMN_ID) tc
WHERE  col1 != 'key'
ORDER  BY "key"
          ,"case"

Some more details as requested:

• dbms_xmlgen.getXMLType returns an XmlType instance which is basically the result of the supplied query string as XML. The format is ROWSET for the root node and ROW for each row. Every column will be an element as well. The 2 selects that I am creating are only returning one value and to makes things easier, I gave them a column alias "V" so that I know which value to pick from the XML.
• extractValue is a function that returns the result of an XPath expression from an XmlType. '/ROWSET/ROW/V' returns the first V node, from the first ROW node that resides under the root node ROWSET.
  <ROWSET><ROW><V>Abc</V></ROW></ROWSET>

Answer 2

Try this:

with data as
(select level l from dual connect by level <= 3)
  select col1,
  'case' || l as "case",
  decode(l,1,col2,2,col3,3,col4) as "values"
  from myTable, data
  order by 1,2;

Cheers

Answer 3

I don't know which parts can change, but this should be a start for you. If the column names can change (key, case 1, etc.) you will have to have another query to get the correct column names. If you have questions feel free to ask:

declare
    v_query VARCHAR2(5000);
    v_case VARCHAR2(255);
    v_colcount PLS_INTEGER;
begin
    -- Get number of columns
    select count(*)
    INTO v_colcount
    from user_tab_columns
    where table_name = 'T1';

    -- Build case statement to get correct value for result column
    v_case := 'case';

    for i in 1 .. v_colcount-1
    loop
        v_case := v_case||' when rn = '||to_char(i)||' then col'||to_char(i+1);
    end loop;

    v_case := v_case||' end result';

    -- Build final query
    v_query := 'select col1 key, ''case ''||rn case, '||v_case||'
        from t1
           cross join (
                select rownum rn
                from dual
                connect by level <= '||to_char(v_colcount-1)||'
            ) cj
        where col1 <> ''key''
        order by key, case';

     -- Display query (would probably be replaced with an insert using execute immediate)
    dbms_output.put_line(v_query);
end;

This produces the following query (which assumes your original table is called t1):

select col1 key, 'case '||rn case, case when rn = 1 then col2 when rn = 2 then col3 when rn = 3 then col4 end result
        from t1
           cross join (
                select rownum rn
                from dual
                connect by level <= 3
            ) cj
        where col1 <> 'key'
        order by key, case

Answer 4

The original table can have a variable number of columns

Really?

The straightforward way is to select and union the parts you want.

select col1 as key, 'case1' as case, col2 as result
from test
where col1 <> 'key'
union all
select col1 as key, 'case2' as case, col3 as result
from test
where col1 <> 'key'
union all
select col1 as key, 'case3' as case, col4 as result
from test
where col1 <> 'key'

Straightforward, but not dynamic.

Later . . .

Based on your comment . . . although I don't think it's necessary.

select col1 as key, (select col2 from test where col1='key') as case, col2 as result
from test
where col1 <> 'key'
union all
select col1 as key, (select col3 from test where col1='key') as case, col3 as result
from test
where col1 <> 'key'
union all
select col1 as key, (select col4 from test where col1='key') as case, col4 as result
from test
where col1 <> 'key'

Oracle 11 also supports UNPIVOT, which I haven't used.