Show results of SQL accross in columns 1

[arewehavingfunyet]

I have a table that looks something like this:

protocol varchar2(12) not null - pk
patient varchar2(12) not null - pk
coldate date not null - pk
testname varchar2(32) not null- pk
testresult_num number(11,5)
testresult_text varchar2(20)

I would like to produce results that for each unique protocol,patient,coldate to display horizontally the testname and result (either the testresult_num or testresult_text, whichever is not null). So the output would look something like this:

Protocol Patient Date WBC RBC PREG
ABCDEFG 12345 20021023 22.22 3.33 Negative
DEFGHIJ 78906 20011022 5.55 Positive

Any help in this is much appreciated! Thanks

 

[fmorel]

Does it mean that the number of columns in the query results will depend on the row values ?

If it is what you want to do, you will have to use PL/SQL to generate dynamic queries.

Otherwise, you can use a reporting tool such as Business Objects and create dynamic cross tables.

 

[arewehavingfunyet]

The number of columns are fixed, in that I know the maximum number of row values that could occur. So I can interpret them using the decode function to flip the result set. However I am not sure how to handle the numeric/varchar2 value in the result set.

 

[fmorel]

You may try

select protocol,patient,coldate, max(decode(testname,'WBC',to_char(testresult_num)||' '||test_result_text,NULL)) as WBC, max(decode(testname,'RBC',to_char(testresult_num)||' '||test_result_text,NULL)) as RBC ...
from yourtable
group by protocol,patient,coldate

 

[arewehavingfunyet]

Thanks, that appears to be doing what I want. Much appreciated!