0

I have a table that has data coming in like my example here. I need to pivot the table but have no key to reference for the column name. I can do it if there are only two values with Max and Min. However I'm going to eventual end up with 20 columns. Right now I'm concatenating them all into a table and then Splitting them apart into another. I was hoping there was a more efficient way of doing this. 

 CREATE TABLE  test (
    ID VarChar(50), 
    PJQ VarChar(50));

    INSERT INTO test
      (ID, PJQ)
    VALUES 
      (1001, 'C982401'),
      (1001, 'F258147'),
      (1000, 'Q985267'),
      (1000, 'R258147'),
      (1000, 'T258476');

And Then I can do this However it only works with two. I'm going to have to do this 20 times.

SELECT ID,
   MAX(PJQ) PJQ1,
   MIN(PJQ) PJQ2
FROM test
GROUP BY ID

I need to be able to do this.

ID      PJQ1     PJQ2      PJQ3
1000    T258476  Q985267   T258476
1001    F258147  C982401

Any help on this problem would be very helpful. Thanks

Here is a link to Fiddle where I have been trying to figure this out. Fiddle

Plaetzler
  • 47
  • 7
  • @Barmar -- it appears to be generated dynamically based on how many rows are in the `test` table. – sgeddes Aug 03 '16 at 16:51

1 Answers1

1

You can use a subquery that increments a variable for each row, and then use this as the pivot key.

SELECT id, 
        MAX(IF(counter = 1, pjq, '')) AS pjq1,
        MAX(IF(counter = 2, pjq, '')) AS pjq2,
        MAX(IF(counter = 3, pjq, '')) AS pjq3,
        ...
        MAX(IF(counter = 20, pjq, '')) AS pjq20
FROM (SELECT id, pjq, 
            @counter := IF(id = @last_id, @counter + 1,
                            IF(@last_id := id, 1, 1)) AS counter
      FROM (SELECT id, pjq FROM test ORDER BY id, pjq) AS x
      CROSS JOIN (SELECT @counter := 0, @last_id = null) AS vars
) AS y
GROUP BY id

FIDDLE

This simple query will work as long as you know the maximum number of columns. If it can be an arbitrary number, you'll need to write a stored procedure that creates dynamic SQL, by first getting the maximum count of values per ID, then generates enough MAX(IF...) columns to get them all.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I use to do this approach but was informed that `@last_id := id` won't set correctly. I deleted my answer, but from my understanding, you should set that in the `if` statement to ensure the correct order of operations. Gordon Linoff corrected me on this one and showed examples of how this could fail... – sgeddes Aug 03 '16 at 17:01
  • Can you provide a link to that? I've never seen it fail, and it's a common idiom. – Barmar Aug 03 '16 at 17:02
  • @GordonLinoff -- you out there to explain this better? From my understanding, the fields in the `select` list don't fire at a specific time, but rather independently. So by setting the `@last_id` in the same `if` statement, you guarantee it sets as you want it to. I'd have to go back through my comments to try to find his examples, but they did make sense at the time even though I had always done it this way before without issue. Now I combine in the `if` statements (or sometimes in `case` statements as it can read better). – sgeddes Aug 03 '16 at 17:05
  • Thanks for the response. I'm concerned about this possibly failing as @sgeddes pointed out but I will test it out anyways. – Plaetzler Aug 03 '16 at 17:25
  • @Barmar -- Here's my conversation with Gordon: http://stackoverflow.com/a/35802584/1073631 – sgeddes Aug 03 '16 at 17:28
  • I've implemented Gordon's fix to ensure proper ordering of assignments. – Barmar Aug 03 '16 at 19:38
  • What If I need this to be a View? You can't do subquery in a view in MySQL. – Plaetzler Aug 04 '16 at 15:52
  • Put the subquery in another view, and use that in the first view. – Barmar Aug 04 '16 at 21:28