excel convert columns to rows or rows to columns

This may be the best thing that I’ve discovered in the last 10 years.  How many times have I had a list of columns that I wanted to convert to rows and vise-versa?  too many…  The common problems is “SELECT * FROM F0101” which goes across the page, and I want it down the page.

The solution is simple.

my classic example is here:

Truncate table PRODDTA.F4072TEM ;

INSERT INTO PRODDTA.F4072TEM (ADAST, ADITM, ADLITM, ADAITM, ADAN8, ADICID, ADSDGR, ADSDV1, ADSDV2, ADSDV3, ADCRCD, ADUOM, ADMNQ, ADEFTJ, ADEXDJ, ADBSCD, ADLEDG, ADFRMN, ADFVTR, ADFGY, ADATID, ADURCD, ADURDT, ADURAT, ADURAB, ADURRF, ADUSER, ADPID, ADJOBN, ADUPMJ, ADTDAY, ADIGID, ADCGID, ADOGID, ADPARTFG) SELECT ADAST, ADITM, ADLITM, ADAITM, ADAN8, ADICID, ADSDGR, ADSDV1, ADSDV2, ADSDV3, ADCRCD, ADUOM, ADMNQ, ADEFTJ, ADEXDJ, ADBSCD, ADLEDG, ADFRMN, ADFVTR, ADFGY, ADATID, ADURCD, ADURDT, ADURAT, ADURAB, ADURRF, ADUSER, ADPID, ADJOBN, ADUPMJ, ADTDAY, 0, 0, 0, ' ' FROM PRODDTA.F4072

I have all of the columns from this create table statement, but I want them down the page, not across.

So, I paste the convents into a spreadsheet and use DATA –> text to columns and I use the comma as the deliminator, so I have all of the columns across the screen:

Then paste special

Oh wow, life is good!!!

This’ll work both ways too (said the vicar to the nun!)