How to transpose a recordset from a MySQL query

I have a script (below) which selects an array from a database and copies the values into excel okay. However, I want to transpose the table from this:

FY year Revenues
FY0 2013 11,019
FY1 2014 11,329
FY2 2015 11,787
FY3 2016 12,359

to This:

FY FY0 FY1 FY2 FY3
year 2013 2014 2015 2016
Revenues 11,019 11,329 11,787 12,359

I have tried playing around with the "Application.Transpose" function and also by calling a transpose function (shown), but with no success. Would someone be able to advise me of the correct syntax to use in the below example to transpose the recordset?

thanks

Please Login or Register  to view this content.

Thanks, but a pivot table is not suitable for my application and I am also aware of the transpose function in excel which is also not suitable. Hence my question was specific in asking for help on the VBA syntax for transposing a recordset and why I provided the context of the script that I am trying to incorporate it into. If anyone knows the answer for this I would be most grateful for their input.

Hi Izandol, you're clearly on the right track, although your code amendments just get me the original table rather than transposing it. I think it may be because 'GetRows' already transposes the data. I therefore changed the code as the function 'TransposeDim' is probably now redundant - from:

Please Login or Register  to view this content.

This seems to transpose the bulk of the table, but field names are still showing up at the top of the columns rather than down the left margin of the rows

Table is now coming thru as:-
FY year Revenues
FY0 FY1 FY2
2013 2014 2015
11,019m 11,329m 11,787m


Whereas is should be:-

FY FY0 FY1 FY2
Year 2013 2014 2015
Revenues 11,019m 11,329m 11,787m

I think you are just an inch away from cracking it

Hi Izandol, you're clearly on the right track, although your code amendments just get me the original table rather than transposing it. I think it may be because 'GetRows' already transposes the data. I therefore changed the code as the function 'TransposeDim' is probably now redundant - from:

Please Login or Register  to view this content.

This seems to transpose the bulk of the table, but field names are still showing up at the top of the columns rather than down the left margin of the rows

Table is now coming thru as:-
FY year Revenues
FY0 FY1 FY2
2013 2014 2015
11,019m 11,329m 11,787m


Whereas is should be:-

FY FY0 FY1 FY2
Year 2013 2014 2015
Revenues 11,019m 11,329m 11,787m

I think you are just an inch away from cracking it

Izandol, you're a genius. Very many thanks indeed. This is so much easier than trying to transpose directly from MySQL. If you ever have a simple way of doing that I would love to know.