When the S-pipe is firmly threaded into place such that it will not leak then it should be able to hold up the shower head. If the shower head is not staying up in position then you do not have the S-pipe threaded in firmly enough.
I installed one of these shower S-pipes in my shower in order for an accommodate an large "rain shower" type head. I removed the original small angle pipe and then inspected how the shower head plumbing was attached up in the wall behind the drywall. This needs to be FIRMLY fastened to the studs in the wall or to a cross member that bridges between two studs. To get a idea of what the shower head plumbing pipe fixture looks here is a picture of a typical installation:
If the shower head plumbing is not securely fastened then steps need to be taken the strengthen the mounting including the possibility of adding a proper cross member between the studs. The reason for this is that hanging a heavy shower head up on a tall S-pipe will transfer a lot of torque into the plumbing line and if not properly mounted could lead to future plumbing failure and leaks.
For installing the S-pipe first make sure that the mating pipe threads in the plumbing are clean and free of any debris. Then you put Teflon plumbers tape onto the end of the S-pipe. Do not put on an excess of the taping. Finally the S-pipe can be threaded loosely into the plumbing pipe. When tightening the S-pipe no wrench is really needed for the S-pipe itself because its length makes it easy to turn it into place. An the ideal situation, if you have access to the shower head plumbing, is to use a plumbing wrench to counteract the thread-in force of the S-pipe so that the rotational torque does not get transferred into the plumbing. If this is not possible then you have to depend on the quality of the plumbing attachment to the studs or cross member in order to hold the plumbing in position during installation.
When you get the S-pipe rotated into position stop turning when it thread joint is tight. If the joint gets tight before the pipe is in the correct position then you may need to torque it into final position if the amount of turning is less than about a quarter of a turn. If more than the quarter turn it may be necessary to remove the S-pipe, re-clean the threads on both the S-pipe pipe and the plumbing pipes and start afresh with just a little more of the Teflon tape on the end of the S-pipe.