Below data is from sql server, need to get output data from the input table. Thanks in advance.......
Input table:
|Id |Company |Name |Amount |Designation |
|1 |x |Alex |5.00 |Clerk |
|1 |x |Thomas |10.00 |Manager |
|1 |y |Bendrick |25.00 |Receptionist |
|1 |y |George |20.00 |Manager |
|1 |asd |Martin |54.00 |Receptionist |
|2 |y |Max |96.00 |Clerk |
|2 |y |John |4.00 |Receptionist |
|3 |esd |Ben |15.00 |Clerk |
|3 |cxz |Long |60.00 |Manager |
|4 |x |Bendrick |29.00 |Receptionist |
|4 |y |Thomas |30.00 |Clerk |
|5 |jkl |Alex |1.00 |Receptionist |
From the above table, First we need to group by Id then we need to group by Company. Here we need to group by Company x and y only, for other values we need to pass Null for varchar values and 0.00 for Amount fields. For Name, Designation fields we need to use max condition and Amount field we need to use sum condition.
Output table:
|Id |Company |Name |Amount |Designation |
|1 |x |Thomas |15.00 |Manager |
|1 |y |George |45.00 |Manager |
|1 |Null |Null |0.00 |Null |
|2 |y |Max |100.00 |Clerk |
|3 |Null |Null |0.00 |Null |
|4 |x |Bendrick |29.00 |Receptionist |
|4 |y |Thomas |30.00 |Clerk |
|5 |Null |Null |0.00 |Null |