CODEWITHSUNDEEP
sqlms-accessvba
Michael
Michael

Reputation: 1777

Bring Vertical Table to Horizontal in Access

I have this table: 

+----+------+
| ID | NAME |
+----+------+
|  1 | A    |
|  1 | B    |
|  1 | C    |
|  2 | A    |
|  2 | P    |
|  2 | D    |
|  2 | X    |
|  3 | F    |
|  3 | 45   |
+----+------+

and i want to transform it to this. (The ID will be unique and the Names for each reoccurring ID of the same ID will be added in a new field.

+----+------+-------+-------+-------+
| ID | NAME | Name1 | Name2 | Name3 |
+----+------+-------+-------+-------+
|  1 | A    | B     | C     |       |
|  2 | A    | P     | D     | X     |
|  3 | F    | 45    |       |       |
+----+------+-------+-------+-------+

Is there a fast way to do this in Access. VBA code is ok. I have tried Pivot and transpose (in Excel) both do not work, or do not give me the desired result. Empty cells like (first row Name3) are ok. Any suggestions, links or code snippets are much appreciated!

Upvotes: 1

Views: 3246

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

This will give you something similar:

TRANSFORM FIRST([Name]) AS FirstOfName
SELECT ID
FROM
    (
        SELECT t1.ID, t1.Name, 'Name' & Format(COUNT(*),"000") AS NewName
        FROM
            YourTable AS t1
            INNER JOIN
            YourTable AS t2
                ON t1.ID = t2.ID
                    AND t1.Name >= t2.Name
        GROUP BY t1.ID, t1.Name
    )
GROUP BY ID
PIVOT NewName

For test data in [YourTable]

ID  Name
--  ----
 1  A   
 1  B   
 1  C   
 2  A   
 2  P   
 2  D   
 2  X   
 3  F   
 3  45

it returns

ID  Name001  Name002  Name003  Name004
--  -------  -------  -------  -------
 1  A        B        C           
 2  A        D        P        X    
 3  45       F

Upvotes: 2

Related Questions