Trading off two rooks for queen and pawn I have hardly if ever seen 2 rooks being traded off for queen and pawn (or for just queen) in common games. 


  Under what conditions is this trade-off advisable? 
  Why is it fairly uncommon?
If anything, it would be advisable to trade one's queen for the opponent's rooks, not vice versa.  But it all depends on the position.  If my opponent's queen is particularly useful to him and dangerous to me, and my rooks haven't been well developed, especially if I'm already ahead in material, I wouldn't hesitate to exchange my rooks for the queen.

Also, in the middle game, if my opponent's rooks are passive, and his king is exposed, I might prefer to exchange away my rooks in favor of having my queen to worry the king with.  In some positions, a queen and minor piece can be more effective than two rooks and a minor piece.  Such positions generally occur during the middle game, when the opponent's many pawns and minor pieces hamper most offensive rook moves.  In contrast, the queen can do very well with her great scope, especially when supported with one or two well-situated minor pieces.

Two rooks for queen and pawn - well, it would depend a lot on the pawn, and on the position.  I would almost certainly trade two rooks for a queen and passed pawn, but I'd regard the opportunity with suspicion if the pawn in question was an underdeveloped doubled pawn already amply guarded against by my pawns.  More than likely, I'd be able to pick up such a pawn later, if it turned out to be necessary.

However, I would consider it a definite material advantage at most times to exchange my queen for two rooks.

The likely reasons why you don't see it much are that 1) the opportunity isn't all that common, and 2) the side getting the two rooks generally becomes stronger, so his opponent will want to avoid it.