Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table


sample data would be

    buildnumber  testname   statusid
    1            testname1  pass 
    1            testname2  fail
    1            testname3  warning     
    2            testname1  fail
    2            testname2  fail
    2            testname3  fail
    3            testname1  fail
    3            testname2  pass
    3            testname3  fail

I would like to get a query to output:

               3         2           1
testname1      fail      fail        pass
testname2      pass      fail        fail 
testname3      fail      fail        warning

What is the best approach to getting this output (buildnumbers as columns)? I am using Oracle 11g and the test names are not known at the time of building the report.

share|improve this question

11g has the pivot construct, which appears to meet your needs:

with pivot_data as (
select buildnumber, testname, status
  from testresults)
select * from pivot_data
 pivot (min(status)
        for buildnumber
        in ('1' as build_1, '2' as build_2, '3' as build_3)
order by 1;

Note the min(status), the pivot requires an aggregate function, so we're working around that requirement.

testname1   pass    fail    fail
testname2   fail    fail    pass
testname3   warning fail    fail
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.