1

I have the following table ‘draftWorker’

select * from draftWorker;

+--------+----------+
| draftID| WorkerID | 
+--------+----------+
|      7 | 8280     |      
|      7 | 7794     |       
|      7 | 4633     |       
|    587 | 8280     |
|    587 | 4633     |
+--------+----------+

The following query is giving me the following result.

SELECT draftID, GROUP_CONCAT(DISTINCT CONCAT(WorkerID)) AS Workers FROM 
draftWorker
GROUP BY draftID;

+-----------+----------------+
| draftID   | Workers        | 
+-----------+----------------+
|      7    | 8280,7794,4633 |      
|     587   | 8280,4633      |
+-----------+----------------+

But I actually need this

+-----------+---------+---------+---------+
| draftID   | Worker1 | Worker2 | Worker3 |
+-----------+---------+---------+---------+
|      7    | 8280    | 7794    | 4633    |
|     587   | 8280    | 4633    |         |
+-----------+---------+-------------------+

Any idea?

[EDIT]

Also Tried the following sql. Still long way to go.

SELECT draftID, Workers From (
SELECT  @position := 0, draftID, 
GROUP_CONCAT(DISTINCT CONCAT(WorkerID, ' As WorkerID', (@position := 
@position + 1) )) As Workers
FROM draftWorker
GROUP BY draftID) AS C;

+-----------+--------------------------------------------------------+
| draftID   | Workers                                                | 
+-----------+--------------------------------------------------------+
|      7    | 8280 As WorkerID1,7794 As WorkerID2,4633 As WorkerID3  |      
|     587   | 8280 As WorkerID1,4633 As WorkerID2                    |
+-----------+--------------------------------------------------------+
Asad
  • 21
  • 1
  • 6
  • Do you really have to do this in MySQL? It will be infinitely easier in a client programming language like PHP or Python. – Barmar Nov 10 '17 at 21:17
  • StackOverflow expects you to [try to solve your own problem first](http://meta.stackoverflow.com/questions/261592), and we also [don't answer homework questions](https://softwareengineering.meta.stackexchange.com/questions/6166). Please update your question to show what you have already tried in a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve). For further information, please see [how to ask good questions](http://stackoverflow.com/help/how-to-ask), and take the [tour of the site](http://stackoverflow.com/tour) :) – Barmar Nov 10 '17 at 21:17
  • You should be able to adapt the stored procedures in the other questions about dynamic pivots. Instead of using the values from a table as the column names, get the maximum of `COUNT(*)`, then write a loop that generates column names like `Worker 1` through `Worker ` – Barmar Nov 10 '17 at 21:18
  • I know its fairly easy from client-side. Unfortunately I need to do it inside mysql (need to join the query with another stored procedure). Thanks for the idea. Actually I am trying to do this right now. – Asad Nov 10 '17 at 21:25

0 Answers0