Show rows into columns directly

[Buyaa]

Hi all, Please help me.
I want to convert all rows from my table into columns (to show on report). I tried pivot, but it is not suitable, there are no any aggregate column, i just need to directly rotate the table as columns become rows and rows become columns.
My table looks like this:

Name | ID | Age | Interest
Donna | 1 | 20 | Music
Paul | 2 | 23 | Sport
Mike | 3 | 18 | Sport
Ann | 4 | 19 | Dancing

my report must show the data like this:
Name: | Donna | Paul | Mike | Ann
ID: | 1 | 2 | 3 | 4
Age: | 20 | 23 | 18 | 19
Interest | Music | Sport | Sport | Dancing

Thank you for help!

[ADezii]

As long as the Data remains simple as to the Number of Records and Fields, so can the solution.

1. Create a Function named fTranspose_2(). This Function will consist of a single Parameter, the Name of the Table whose Data you wish to Transpose and display. The Function will actually Transpose the Data (flip Rows and Columns), and build a String consisting of all the Data.
   

   Function fTranspose_2(strSource As String)
   On Error GoTo fTranspose_2_Err
   Dim MyDB As DAO.Database
   Dim rstSource As DAO.Recordset
   Dim inFldCtr As Integer
   Dim strBuild As String
    
    
   Set MyDB = CurrentDb()
   Set rstSource = MyDB.OpenRecordset(strSource)
   rstSource.MoveLast: rstSource.MoveFirst
    
    
   'The Transposing is done within this Nested Structure
   With rstSource
     For inFldCtr = 0 To .Fields.Count - 1
      strBuild = strBuild & .Fields(inFldCtr).Name & Space$(10)
       Do While Not .EOF
           strBuild = strBuild & .Fields(inFldCtr).Value & Space$(10)
         .MoveNext
       Loop
         strBuild = strBuild & vbCrLf
         .MoveFirst
     Next inFldCtr
   End With
    
   rstSource.Close
   Set rstSource = Nothing
    
   fTranspose_2 = strBuild
    
   Exit_fTranspose_2:
     Exit Function
    
   fTranspose_2_Err:
     MsgBox Err.Description, vbExclamation, "Error in fTranspose()"
       Resume Exit_fTranspose_2:
   End Function

2. On your Report, create a Text Box in the Detail, or other Section, the width of the Report itself. The Report should probably be in Landscape Orientation.
3. Set the Can Grow Property of the Text Box to Yes.
4. Set the Control Source of the Text Box equal to the Function Call, as in:
   

   =fTranspose_2("tblTest")

5. Download the Attachment to get a much clearer picture of the solution.
6. You can also Format the Data in the Text Box for a better look.

[Stewart Ross]

There is no built-in way to do this in Access nor in SQL itself. This is not what relational databases were designed to do. In relational theory each row shown in a table or query should be unique, and represent either atomic (indivisible) data about the unique entry (such as a name or interest as you have shown), or aggregate data about the unique row entry (for example, a summary row in a purchase system showing a customer's basic details alongside the number of items the customer has purchased this year and their total value).

Transposing data in the way you suggest breaks relational rules because the row data is no longer atomic or aggregate data for a single unique entity; instead it is attempting to make the row consist of a theoretically unlimited number of columns with no commonality of unique entity at all.

As this is not what relational database are designed to do there is no built-in method of doing so, by SQL or any other means. You can adopt a programmed solution, as ADezii recommends, but be aware that you need to have very limited numbers of rows for such approaches to work. In database tables consisting of thousands of rows there is no way this can be advised.

In Excel it is possible to use the Transpose facility which can turn round all the row-oriented data into column-oriented format, and it is Excel I'd suggest you look at and not Access to do what you ask.

-Stewart

PS ADezii shows you a code solution which you really should look at - but I would still say Excel is an easier option in general...

[Rabbit]

You're not aggregating any data but you can pivot and "aggregate" on the max or first.

[Buyaa]

I have tried such aggregate functions, but it is not working and i thought pivot is not suitable for this purpose. If possible please show me the solution, thank you

[Buyaa]

Thank you ADezii. Your solution seems to work. But my db is MS Access and language I'm using .Net C# (report wizard). I don't know how to call function in the report. Anyway I'll go trough carefully on your solution and maybe i could found the solution. Thank you

[ADezii]

Try Reposting this question in the appropriate Forum, then see what happens.

[Rabbit]

You would have to do a 3 pivots and union them together if you want to do it all in SQL.