Oracle - split single row into multiple rows

Question

In Oracle database, I have a table which contains results for a number of different test types.

Table:

object_tested, test_date, test_a, test_a_result, test_b, test_b_result
TmpObj         timestamp, value1  value2         value3  value4

I need to export these test results, but create a separate row for each test, so something like:

object_tested, test_date, test,   test_result
TmpObj         timestamp, value1, value2
TmpObj         timestamp, value3, value4

What would be the quickest way to do this? Maybe a UNION or JOIN?

score 5 · Accepted Answer · answered Sep 17 '14 at 11:06

The simplest way is with a union all:

select object_tested, test_date, test_a as test, test_a_result as test_result
from table t
union all
select object_tested, test_date, test_b as test, test_b_result as test_result
from table t;

If you want the type of test in the output:

select object_tested, test_date, 'a' as test_type, test_a as test, test_a_result as test_result
from table t
union all
select object_tested, test_date, 'b' as test_type, test_b as test, test_b_result as test_result
from table t;

Oracle 11 also supports the unpivot operator which does something similar. If you have a really large table and care about performance, unpivot or a method using join can work.

Thanks for this, the UNION ALL (possible UNION if they want me to remove duplicates) seems like the simplest way to build this query. — KS1, Sep 17 '14 at 13:10

score 2 · Answer 2 · edited Apr 15 '15 at 12:57

In Oracle Database, the pivot and unpivot operators enable you to break out a row into many columns, or collect up columns into fewer rows.

WITH t(object_tested, test_date, 
       test_a, test_a_result, test_b, test_b_result) AS
   (SELECT 'TmpObj' ,
    'timestamp',
    'value1' ,
    'value2' ,
    'value3' ,
    'value4'
    FROM dual
   )
SELECT *
FROM t unpivot ((test_result,test)
      FOR category IN ( 
                         (test_a_result,test_a) AS 'a' ,
                         (test_b_result,test_b) AS 'b' 
                      ) 
               )

Pivot and unpivot operators oracle demo:

http://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

Oracle - split single row into multiple rows

2 Answers2

Linked