The fishing Guru will give it to you,he can be found in a house on Silence Bridge (Kanto near route 11)
In HG/SS you can only get the Super Rod in the post-game once you're able to reach Kanto. You'll get it from the younger fishing brother in his house on Route 12, directly south of Lavender Town. This is actually the same house where you get the Super Rod in FRLG too, so it might feel familiar! Screenshot of the house below:
(Source: Guide Strats)
