A great example of of how to make use of abandoned and 'useless' buildings and structures in a sustainable way is this water tower in Jaegersberg, Denmark, which was converted into a student housing complex.
This structure was left unused until the architects Dorte Mandrup Arkitekter ApS won a competition to renovate it into a 10-storey multi-purpose building for student housing and a youth centre, which was completed in 2006. The top 5 floors are reserved for student housing and feature roomy and bright apartments with multi-faceted protrusions that extend the living space as well as draw more light into the apartments. Daylighting through the extensions brightens the interiors, providing a warm and sunny space with a view of the surrounding landscape.
The bottom floors are used for a youth centre that features several large multipurpose rooms. This section of the building has tall windows and coloured panels to make the space more playful and alive. Large garage-style doors on the very bottom floor can be raised to open the room to the outdoor playground.