Tearing my hair out!

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

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.