MS Access PIVOT with User Defined Field
- by user2535359
Any of you good souls please help!!
I need to query the source table shown in the below. (NULL are blank fields)
UNUM, Ticket, Overflow
1 , 135 , NULL
1 , 136 ,NULL
1, 137, NULL
1, 138, NULL
1, NULL, 2b
2, 135, NULL
2, 136, NULL
2, 137, NULL
3, 135, NULL
3, 136, NULL
3, 137,NULL
3, 138, NULL
3, 139, NULL
3, 140, NULL
3, NULL, 66a
4, NULL, 12a
5, NULL, 14a
I need to generate the output as shown below.
UserNum, Ticket1, Ticket2, Ticket3, Ticket4, Ticket5, Ticket6, Ticket7, Ticket8, Ticket9, Overflow
1, 135, 136, 137, 138, Null, Null, Null, Null, Null, 2b
2, 135, 136, 137, Null, Null, Null, Null, Null, Null, Null
3, 135, 136, 137, 138, 139, 140, Null, Null, Null, 66a
4, Null, Null, Null, Null, Null, Null, Null, Null, Null, 12a
5, Null, Null, Null, Null, Null, Null, Null, Null, Null, 14a
The source table has multiple tickets assigned to user. There are always maximum of 9 tickets.
The user either has a ticket or an overflow but here can be only overflow per user.
I am having issue pivoting the data in Ticket column to pre-defined field names like Ticket1, Ticket2...
