I want to get the data of a table without duplicating the column name but getting the other column values of the same distinct column,
for example, this is my table
id | name | position | salary
1 | hashem | software | 1000
2 | hashem | developer| 2000
3 | hashem | backend | 3000
I am expecting the output to be like
name | position1| position2 | position3 | salary1 | salary2 | salary3
hashem| software | developer | backend | 1000 | 2000 | 3000
What is the perfect query to do this?
UPDATED: SOLUTION NO NEED TO GO FOR PIVOT:
THE QUERY TO BE LIKE THIS:
SELECT
sub_service_name,
substring_index(substring_index(positions,',',-3),',',1) as position1,
substring_index(substring_index(positions,',',-2),',',1) as position2,
substring_index(substring_index(positions,',',-1),',',1) as position3,
substring_index(substring_index(salaries,',',-3),',',1) as salary1,
substring_index(substring_index(salaries,',',-2),',',1) as salary2,
substring_index(substring_index(salaries,',',-1),',',1) as salary3
from
(SELECT tablename.name, GROUP_CONCAT(tablename.position) as 'positions', GROUP_CONCAT(tablename.salary) as 'salarie' from tablename groub by name ) as result
