How do I pivot this source table

Question

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.

Where do the column names `Emp1`, `Emp2` come from in the original table? — Barmar, Nov 12 '21 at 21:32
I think you just want `GROUP_CONCAT(Name)` and `GROUP BY Position`. — Barmar, Nov 12 '21 at 21:33
Was aliasing the new columns. 4 columns (worse case position 2 has 4 employees hence Emp1, Emp2, etc. — ads248, Nov 12 '21 at 23:02
A pivot moves repeated data values into the column names. There's no `Emp1` value in the table. — Barmar, Nov 12 '21 at 23:03
Because he’s the 1st employee of 4 in position 2. Popeye, Olive, Scooby then Homer — ads248, Nov 12 '21 at 23:06
So where are the column names supposed to come from? Just sequential numbers up to the maximum number of repetitions? You'll need to use dynamic SQL to get the maximum count. — Barmar, Nov 12 '21 at 23:11
Do you have to do this in SQL? It will be much easier in a programming language. — Barmar, Nov 12 '21 at 23:12
Perhaps pivot was the wrong word then. Popeye, Olive, scooby & Homer are all against Position 2. I want to report 1 line for position 2 and transpose those 4 occupants wide instead of long — ads248, Nov 12 '21 at 23:13
`SELECT position, GROUP_CONCAT(name ORDER BY EmpNumber) AS names FROM table GROUP BY position` — Barmar, Nov 12 '21 at 23:14
Yep 1-4 just represented the number of occurrences on position 2. There would never be more than 4 — ads248, Nov 12 '21 at 23:15

score 0 · Accepted Answer · answered Nov 12 '21 at 23:22

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

How do I pivot this source table

1 Answers1