MS Access PIVOT with User Defined Field
Posted
by
user2535359
on Stack Overflow
See other posts from Stack Overflow
or by user2535359
Published on 2013-06-29T22:18:21Z
Indexed on
2013/06/29
22:21 UTC
Read the original article
Hit count: 144
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...
© Stack Overflow or respective owner