The result of a table is as follows:
| Name | Postion | subordinate | Country | Rate |
|---|---|---|---|---|
| John | Manager | Jose | IN | 50 |
| John | Manager | Raju | SR | 25 |
| John | Manager | CRISS | IN | 30 |
| JOHN | Manager | DON | BN | 10 |
| John | Manager | Jose | IN | 300 |
| John | Manager | Raju | SR | 85 |
| John | Manager | CRISS | IN | 450 |
| JOHN | Manager | DON | BN | 100 |
| MATHEW | HRM | ALI | BH | 20 |
| MATHEW | HRM | Neethu | IN | 20 |
| MATHEW | HRM | SEENA | IN | 20 |
And the Actual Result need to get is as follows,
| Name | Postion | subordinate | Country | Rate |
|---|---|---|---|---|
| John | Manager | Jose,Raju,Criss,DON | IN,SR,BN | 1050 |
| MATHEW | HRM | ALI,Neethu,SEENA | BH,IN | 60 |
The querry i tried is
Select A.Name as Name, A.Position as Postion, STRING_AGG(B.Subordinate,',') as Sabordinates ,STRING_AGG(A.Country,',') as Country, SUm(C.Rate) from
Emp A
left join Sabordinate B on A.ID= B.SabId
Left join Cost C on C.SId=B.SabId
Group by A.Name,A.Position
But am getting the result as follows ,
| Name | Postion | subordinate | Country | Rate |
|---|---|---|---|---|
| John | Manager | Jose,Raju,Criss,DON,Jose,Raju,Criss,DON | IN,SR,IN,BN,IN,SR,IN,BN | 1050 |
| MATHEW | HRM | ALI,Neethu,SEENA | BH,IN,IN | 60 |
it is repeating the subordinate name and one country twice or triple( times of name repeating is repeating as much)
i need only one name and country only one time if it is repeating