CODEWITHSUNDEEP
sqloracle-database
KS1
KS1

Reputation: 1039

Oracle - split single row into multiple rows

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?

Upvotes: 1

Views: 8231

Answers (2)

Aramillo
Aramillo

Reputation: 3216

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 5

Related Questions