float to varchar with no trailing zeros
SQL Server 2012 and up support FORMAT, which enables .NET-like format strings, making this a rather easy task; just use

FORMAT(Num, '0.#####')


Working SQL Fiddle here.