0

In MySQL I've got a table that looks like this:

ID     QBATCH    USERID    SEQUENCE     EVENT_DATA
--     ------    ------    --------     ----------
448      56       229         1          BEFORE
464      80       229         1          AFTER
496      80       229         2          AFTER

1391    128       955         1          AFTER
1448    148       955         2          AFTER
1693    148       955         3          AFTER
2549    148       955         4          AFTER

With a given userid, there could be up to 5 records -- 1 'before' and up to 4 'after' records. What I'd like to do is unstack the data so it looks something like this:

ID1    QBATCH1    USERID1    SEQUENCE1    EVENT_DATA1    ID2    QBATCH2    USERID2    SEQUENCE2    EVENT_DATA2    etc...
448      56        229          1          BEFORE        464     80         229          1          BEFORE


1391    128        955          1          AFTER         1448    148        955          2          AFTER

I'd like to (somehow) ensure that all the 'before' records are in the first position without relying on the 'sequence' number, if possible (because the 'event_data' doesn't always say 'BEFORE').

I hope I'm asking this correctly and that someone has an answer. If I didn't ask correctly, please be patient. Since I don't know MySQL that well, the only things I've tried were to export the data and work with them in other applications (Excel, Access, etc.) but nothing seems to be working. I'm sure there has to be a way of doing this in MySQL.

Any help?

EDIT: The issue is that I can't rely on the 'sequence' number. I need to say something like 'take the first record for this userid and put it in the first position. Take the second record for this userid and put it in the second position -- regardless of the sequence number'

Mr_Thomas
  • 857
  • 3
  • 19
  • 39
  • 1
    put some code you using , it will helps to modify – Dhanush Bala Oct 09 '14 at 13:37
  • I don't have any code. I don't even have any idea of where to begin. – Mr_Thomas Oct 09 '14 at 13:51
  • Why do you have meaningless fields in your data? It seems like you should first work on getting a reliable column to sort based on. If you have this, you likely would not need this pivot table type output you are looking for. – Mike Brant Oct 09 '14 at 14:00
  • What identifies "first record", "second record", etc.? – Ed Gibbs Oct 09 '14 at 14:03
  • In my example above, 'first record' is ID 448 -- 'second record' is ID 464 -- 'third record' is ID 496, for the first userid (229). – Mr_Thomas Oct 09 '14 at 14:08
  • If I read correctly, you want conditional sorting: http://stackoverflow.com/questions/4346823/mysql-order-by-case-issue – Bartłomiej Wach Oct 09 '14 at 14:22
  • That's a cool idea -- however, I don't want to have to specify all 2000 userid's (in the `when ID = 27` part of the statement) – Mr_Thomas Oct 09 '14 at 14:25

0 Answers0