I want to merge 2 rows in I-Report

[afaquecena]

Hi Friends,

I am stucked in a situation where with my SQL query I am getting an output such as-:

Fname	Lname	Event1	Event2
abc	xyz	No	Yes
abc	xyz	Yes	No

So this is the output which iam getting with my SQL query and I want my output to be of single row so that the end user have a glance at the report and can easily guess which are the event that guy has attended.

Fname	Lname	Event1	Event2
abc	xyz	Yes	Yes

 

 

 

 

[szaharia]

You should modify the query, in order to get a single row per each name. For instance:

select Fname, Lname, max(Event1) as evt1, max(Event2) as evt2 from my_table group by Fname, Lname

[anji.viper]

You can try with this query.. It may be a bit long but since your data is simple, it should suit your needs well. It basically checks whether there's at least 1 Yes in each event then outputs Yes in the same row for the Fname and Lname in the list.

select x.Fname, x.Lname, (case when x.evt1 > 0 then 'Yes' else 'No' end) as event1, (case when x.evt2 > 0 then 'Yes' else 'No' end) as event2from (select Fname, Lname, count(case when Event1 ='Yes' then 1 else 0 end) as evt1, count(case when Event2 ='Yes' then 1 else 0 end) as evt2from table_namegroup by Fname, Lname) as x[/code]