I've got two tables:

  1. tbl_sms
  2. tbl_bids

The above mentioned two table holds something similar to 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 individuals two tables I am trying to offer 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'd 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 provides me the right result but they're not pivoted generate an income would like it to be.

Can please someone assist me with that one. I welcome any solution whether it's a full extended query or perhaps a procedure.

In SQL Server this is simple to accomplish utilizing a ROW_NUMBER() function or CROSS APPLY construct. In MySQL this really is harder.

One option would be to emulate ROW_NUMBER() in MySQL using variables. By doing this you'll be able to return the bids for every publish id rated on bid time, and obtain the consumer_ids. After that on it's an easy matter to LEFT JOIN the SMS time for you to the publish_id/user_id combination. Following a example within the link, the code is going to be something similar to:

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 may then come with an output such as 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 are able to store this create 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)

Regrettably because of a MySQL limitation you cannot use multiple references towards the same temporary table inside a query, so you will need 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...

When the recordset is large, its smart off and away to assign a (primary key) index on Publish_ID to accelerate the pivoting query.

You can now 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 includes a point though, it's more scalable to construct the body around an unpivoted table. Therefore if you don't have to pivot, don't.