MySQL Pivot rows to columns for a similar record

Question

I have got two tables:

1. tbl_sms
2. tbl_bids

The above two table holds something like this:

tbl_sms:

Post_ID | User_ID | SMS_Sent_DT
---------------------------------
123     |   007   |   2010-05-14 10:15:25
123     |   008   |   2010-05-14 10:18:32
123     |   009   |   2010-05-14 10:23:05
123     |   010   |   2010-05-14 10:23:06

tbl_bids:

Post_ID | User_ID | Bid_DT
--------------------------
123     |   010   |   2010-05-14 10:27:25
123     |   008   |   2010-05-14 10:28:32
123     |   009   |  2010-05-14 10:28:47
123     |   007   |   2010-05-14 10:35:06

With those two tables I'm trying to achieve the following:

Post_ID | First_BID_Time | First_BID_SMS_TIME | Second_BID_Time | Second_BID_SMS_Time | Third_BID_Time | Third_BID_SMS_Time
-----------------------------------------------------------------------------------------------------------------------------------
123     | 2010-05-14 10:27:25 | 2010-05-14 10:23:06 | 2010-05-14 10:28:32 | 2010-05-14 10:18:32 | 2010-05-14 10:28:47 | 2010-05-14 10:23:05

---

The query I had written is:

   SELECT b.post_id,
          sms.message_sent_at as notif_sent1,
          b.message_sent_at as notif_accepted1,
          DATEDIFF(b.message_sent_at, sms.message_sent_at) AS delay1
     FROM tbl_bids b
LEFT JOIN tbl_sms_status sms ON (sms.jobid = b.post_id AND b.user_id = sms.userid)
    WHERE b.post_id = sms.jobid
 ORDER BY b.post_id ASC

That gives me the correct result but they are not pivoted the way I want it to be.

Can please someone help me out with this one. I welcome any solution be it a full lengthy query or a procedure.

Answer 1

In SQL Server this would be easy to accomplish using a ROW_NUMBER() function or CROSS APPLY construct. In MySQL this is harder.

One solution is to emulate ROW_NUMBER() in MySQL using variables. This way it is possible to return the bids for each post id ranked on bid time, and get the user_ids. From there on it is an easy matter to LEFT JOIN the SMS time to the post_id/user_id combination. Following the example in the link, the code will be something like:

SELECT tmp.Post_ID, tmp.ranking, tmp.user_ID, tmp.Bid_DT, s.SMS_DT
FROM ( 
  SELECT 
    b.Post_ID, b.user_ID, b.Bid_DT, 
    IF( @prev <> ID, @rownum := 1, @rownum := @rownum+1 ) AS ranking, 
    @prev := ID 
  FROM tbl_bids b 
  JOIN (SELECT @rownum := NULL, @prev := 0) AS r 
  ORDER BY b.Post_ID, b.BID_DT 
) AS tmp
LEFT JOIN tbl_sms s
  ON tmp.Post_ID = s.Post_ID AND tmp.user_ID = s.user_ID 
WHERE tmp.rank <= 3 -- Top 3, adjust when more are necessary 
ORDER BY post_ID, ranking; 

You'll then have an output like this:

Post_ID | Ranking  | User_ID | Bid_DT                 | SMS_DT
---------------------------------------------------------------------------
123     |    1     |   010   |   2010-05-14 10:27:25  | 2010-05-14 10:23:06
123     |    2     |   008   |   2010-05-14 10:28:32  | ....
123     |    3     |   009   |   2010-05-14 10:28:47  | ....
123     |    4     |   007   |   2010-05-14 10:35:06  | ....
124     |    1     | .......

You can store this result in a temporary table:

CREATE TEMPORARY TABLE RankedBids(Post_ID INTEGER, Ranking INTEGER, User_ID INTEGER, Bid_DT DATETIME, SMS_DT DATETIME)
INSERT INTO Rankedbids SELECT.... (use above query)

Unfortunately due to a MySQL limitation you can't use multiple references to the same temporary table in a query, so you'll have to split out this table by ranking:

CREATE TEMPORARY TABLE RankedBids1(Post_ID INTEGER, User_ID INTEGER, Bid_DT DATETIME, SMS_DT DATETIME)
CREATE TEMPORARY TABLE RankedBids2....
INSERT INTO Rankedbids1 SELECT Post_ID, User_ID, Bid_DT, SMS_DT FROM RankedBids WHERE Ranking = 1
INSERT INTO RankedBids2...

If the recordset is very large, it pays off to assign a (primary key) index on Post_ID to speed up the pivoting query.

Now you can pivot this data:

SELECT R1.Post_ID, R1.Bid_DT AS Bid_DT1, R1.SMS_DT AS SMS_DT1 .... 
FROM RankedBids1 R1
LEFT JOIN RankedBids2 R2 ON R1.Post_ID = R2.Post_ID
LEFT JOIN RankedBids3 R3 ON ........

OMG Ponies has a point though, it's more scalable to build your system around an unpivoted table. So if you don't need to pivot, don't.