It's definitely case by case, as Kriswall notes. The wording for each ability should make it clear if the pile in and attack are immediate/back to back or just twice but still alternating with the opponent.
(well, maybe clear is an overstatement, but after looking through all of the warscrolls it does seem like
GW took care to specify when a double pile in + attack could occur immediately)
Immediate/back to back:
Colossus of War: After a Bone Giant has attacked in the combat phase for the first time roll a dice. Add one to the result if a Necrotect from your army is within 18". On a 5 or more, that Bone Giant can immediately pile in and attack for a second time that turn.
Twice, but still alternating with the opponent:
Vanhel’s Danse Macabre: The undead are filled with magical energy that causes them to jerk forwards and attack with tireless, unnatural speed. Vanhel’s Danse Macabre has a casting value of 6. If successfully cast, pick a Skeleton, Mordant or Zombie unit within 18"; that unit can pile in and attack twice in your next combat phase.