How do I pivot this source table-CodePudding

I have a table like this:

Position      | EmpNumber | Name         | Status
1             | 1234      | Fred         | Active
2             | 1235      | Popeye       | Active
2             | 1236      | Olive        | Active
2             | 1237      | Scooby       | Active
2             | 1238      | Homer        | Active
3             | 1239      | Bart         | Active
3             | 1240      | Tom          | Active
4             | 1241      | Jerry        | Active
5             | 1242      | Bugs Bunny   | Active

I want to query that, and pivot it, so I only get the position (column 1) once and columns for each EmpNumber (column 2) aliased as Emp1, Emp2 etc. the worse case will ever be 4 numbers against each position. So my required results would be:

Position | Emp1       | Emp2  | Emp3   | Emp4
1        | Fred       | NULL  | NULL   | NULL
2        | Popeye     | Olive | Scooby | Homer
3        | Bart       | Tom   | NULL   | NULL
4        | Jerry      | NULL  | NULL   | NULL
5        | Bugs Bunny | NULL  | NULL   | NULL

I want all of the attributes for each row, maybe as single columns or maybe concatenated into a single field i.e. "1234,Fred,Active", but have just used the Name column above to keep it simple for now.

Also interested in how to make it dynamic just in case additional rows ever featured against the position.

CodePudding user response：

Use GROUP_CONCAT() in a subquery to combine all the names from the same position into a comma-separated list, then use SUBSTRING_INDEX() in the main query to extract them into separate columns.

SELECT position, 
    SUBSTRING_INDEX(names, ',', 1) AS Name1,
    IF(namecount > 1, SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', 2), ',', -1), NULL) AS Name2,
    IF(namecount > 2, SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', 3), ',', -1), NULL) AS Name3,
    IF(namecount > 3, SUBSTRING_INDEX(SUBSTRING_INDEX(names, ',', 4), ',', -1), NULL) AS Name4
FROM (
    SELECT position, GROUP_CONCAT(name ORDER BY EmpNumber) AS names, COUNT(*) AS namecount
    FROM yourTable 
    GROUP BY position
) AS subquery