Multiple DB rows form one report row

[scb1776]

I need to combine two rows from a table in the database to form one row in the report.

The setup :
Table1

ID ParentID Label Test Result
1 0 General
2 1 General TestA ResultA
3 1 General TestB ResultB
4 0 OtherInfo

In my report I want to see

General TestA ResultA TestB ResultB

Applied : OtherInfo

Our company has put in a policy of no command objects (we have problems using them in multi-schema environments)
I am having problems just using an inner/outer join with multiple inclusions of the same table, as I keep needing to restrict the data from the sub tables, but still need all of it from the first inclusion.

Does anyone have any suggestions?

Thanks.

 

[IanWaterman]

You could try using a Cross tab

Ian