Wish to flip the axis of a query rendered in Excel

Question

I am creating a simple Excel file that will show the results of an SQL query in a table. The only issue is that I wish to display the columns horizontally rather than vertically I.E.:

Column name | val | val | val |

Column name | val | val | val |

Column name | val | val | val |

I have researched options and the most common I find is to use the Transpose option under edit, but this is not an option for me as the data does not reset automatically. I cannot seem to find the option under the configurations of format or during the creation of the query. I created the query itself in Micorsoft Query and it is below (With slight trimming to make it easier to read):

SELECT SMPL_HDR.SMPL_HDR_KEY, SMPL_HDR.SMPL_DATE, SMPL_HDR.GROWER_NAME, SMPL_HDR.BREEDCROSS,
SMPL_HDR.FLOCK_AGE, SMPL_HDR.HOUSE_NUMBER, SMPL_HDR.TEST_TYPE, SMPL_HDR.LOCATION,
SMPL_HDR.USER_ID, SMPL_HDR.DATE_ENTERED, SMPL_DTL.SMPL_DTL_KEY, SMPL_DTL.PAWS_WGT, 
SMPL_DTL.NECK_SKIN_WGT, SMPL_DTL.NECK_WGT, SMPL_DTL.HEART_WGT, SMPL_DTL.LIVER_WGT, 
SMPL_DTL.PLD_GIZZARD_WGT, SMPL_DTL.FAT_WGT, SMPL_DTL.PRE_CHILL_WGT, SMPL_DTL.PST_CHILL_WGT, 


FROM EYIS.dbo.SMPL_DTL SMPL_DTL, EYIS.dbo.SMPL_HDR SMPL_HDR
WHERE SMPL_DTL.SMPL_HDR_KEY = SMPL_HDR.SMPL_HDR_KEY AND ((SMPL_HDR.DATE_ENTERED=?))

I would apreciate any help you can offer. It feels like there's a configuration option right in front of me I am not seeing.

Answer 1

If you're at all comfortable with VBA macros, you could create a macro to transpose the results and call that macro after you run your query. (Not sure which event you'd trap. You didn't provide a lot of detail about how you populate your worksheet.) My VBA is very rusty, but I took a quick stab at it and here is what transposed some test data for me...

Option Explicit

Public Sub TransposeCells()
    Dim iRow As Integer
    Dim iCol As Integer
    Dim iMaxRow As Integer
    Dim iMaxCol As Integer
    Dim Sheet As Excel.Worksheet
    Dim newSheet As Excel.Worksheet
    Dim Rng As Excel.Range
    Dim newRng As Excel.Range

    iRow = 1
    iCol = 1

    Set Sheet = Application.ActiveSheet
    ' Find maximum boundaries
    Set Rng = Sheet.Cells(iRow, iCol)
    While (Rng.Text <> "")
        iRow = iRow + 1
        Set Rng = Sheet.Cells(iRow, iCol)
    Wend
    iMaxRow = iRow - 1
    iRow = 1

    Set Rng = Sheet.Cells(iRow, iCol)
    While (Rng.Text <> "")
        iCol = iCol + 1
        Set Rng = Sheet.Cells(iRow, iCol)
    Wend
    iMaxCol = iCol - 1
    iCol = 1

    ' Add new sheet for the results
    Set newSheet = Worksheets.Add()
    newSheet.Name = "Transposed"

    ' Transpose the results
    For iRow = 1 To iMaxRow
        For iCol = 1 To iMaxCol
            Set Rng = Sheet.Cells(iRow, iCol)
            Set newRng = newSheet.Cells(iCol, iRow)
            newRng.Select
            ActiveCell.Value = Rng.Text
        Next iCol
    Next iRow
End Sub