Converting multiple rows in a table into a single one

[suhas_bhat]

Hi all,
     I have the following 2 tables Table A and Table B. Table B is a table that updates with user input from front end, in this case user changed the Cflg to Y and the Hflg to null for the same ID, it updates as a new row in the table with new SID.

Table A          Table B
ID                   SID  ID  Hflg  Cflg
1                     01   1     Y     null
2                     02   1    null    Y

At report level i need to join these 2 tables for report output
After join on ID the result would be
ID Hflg Cflg
1    Y    null
1   null   Y

Users however prefer seeing data in a single row like this
ID Hflg Cflg
1    Y      Y

Can anyone please point me in the right direction. How can this be done in Cognos 10?

[blom0344]

Very simple. Use an aggregate (min / max) against the Hflg / Cflg dataitems so that data is rolled up against the Id

[suhas_bhat]

thank you blom0344, your solution worked.  :)

While i was waiting for a reply i tried out another more complex logic. Please share your thoughts on this.

Lets call the result set of the join between Table A and Table B as Query A.
I took a copy of Query A and named it Query B.
Now the main report list report pointed to Query A and the data items Hflg,Cflg i put them inside repeaters. The repeater points to Query B and master detail join on ID. the Hflg,Cflg basically concatnate over each other and give the desired output.

I don't know how safe this soln is so am definitely going with blom0344s version. Thanks again  :)

[blom0344]

master-detail designs are suspect to performance issues. Everything that can be handled in the query will be superior in terms of performance!!