java - Crosstab query with TRANSFORM and PIVOT is repeating rows -

the query below used retrieve results in database inserted jtable.

transform abs(a.present) select e.id, e.firstname, e.lastname, e.position, e.rate  employees e left join attendance on e.id = a.empid group e.id, e.firstname, e.lastname, e.position, e.rate, a.present  pivot a.dateattended 

the results retrieved query can seen below.

now, merged results. problem in query arises when result date columns differ, (e.g. 1 | 0, or 0 | 1). id result being duplicated (encircled in red). want ask possible ways on how can merged results seen in right part of image. thank in advance.

your problem want transform (i.e., report value of) a.present have included field group clause. try using transform first(abs(a.present)) , removing a.present group clause, i.e.,

transform first(abs(a.present)) select e.id, e.firstname, e.lastname, e.position, e.rate  employees e left join attendance on e.id = a.empid group e.id, e.firstname, e.lastname, e.position, e.rate pivot a.dateattended