1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Rep Power

    Rearrange columns with query

    Does anyone know how I'd perform a query in order to achieve the following, based on the attached image...

    I want to stack each of the 'cm' columns by 'TransectNumber', so the resulting table/query will have TransectNumber (1,2,3 etc) as columns and the associated 0-90cm values as entries for each column.

    So based on the screenshot I want to have 3 columns called (e.g.) Transect1, Transect2 and Transect3, and each column would have 100 entries.

    Hope that makes sense.

    Many thanks

    Last edited by stoyleg; October 9th, 2012 at 06:35 PM. Reason: Bad title
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Rep Power
    The correct answer is 'normalise your data and handle the display logic at the application level'.

    If you want someone to suggest how you can emulate this using the present structure, I suggest you provide CREATE and INSERT statements for just 3 transects, 3 columns, and 3 results in each, together with the result set you'd expect from your query. Perhaps someone will be kind enough then to offer a non-normalised approach and maybe even demonstrate what a properly normalised table might look like.

IMN logo majestic logo threadwatch logo seochat tools logo