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?

Gordon Linoff · Accepted Answer · 2014-09-17 11:06:06Z

5

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.

answered Sep 17, 2014 at 11:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KS1 Over a year ago

Thanks for this, the UNION ALL (possible UNION if they want me to remove duplicates) seems like the simplest way to build this query.

Eric Leschinski · Accepted Answer · 2015-04-15 12:57:14Z

2

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

edited Apr 15, 2015 at 12:57

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

answered Sep 17, 2014 at 12:46

Aramillo

3,2263 gold badges27 silver badges51 bronze badges

Collectives™ on Stack Overflow

Oracle - split single row into multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related