Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have a table

  CREATE TABLE "AUTOMATION"."TESTRESULTS" 
   (
    "BUILDNUMBER" VARCHAR2(30 BYTE), 
    "TESTNAME" VARCHAR2(100 BYTE), 
    "STATUS" VARCHAR2(100 BYTE) 
   ) 

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
add comment

1 Answer

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.

TESTNAME    BUILD_1 BUILD_2 BUILD_3
testname1   pass    fail    fail
testname2   fail    fail    pass
testname3   warning fail    fail
share|improve this answer
add comment

Your Answer

 
discard

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.