I want to move data from rows to columns in a SFMC SQL Automation so that we can easily see all the bookings one customer has made, all in one row.
We collect bookings on one unique reservation number (column 1 in example below) but where a customer has booked in for multiple programs (column 3 and 4), they are currently split out over multiple rows, eg: our DE currently looks like this:
Master DE name: Programs Reservation | Name | Program | Time | 12345 | Anna | Workshop A | 9am | 12345 | Anna | Workshop B | 9.30am | 12345 | Anna | Workshop C | 10am | 56789 | Brian | Workshop D | 10.30am | 11223 | Colin | Workshop C | 10am | 11223 | Colin | Workshop A | 9am |
And I'd like it to look like where we can see all the programs one customer has booked in one row with the time of the workshop in the column next to the program:
New DE: Reservation | Name | Program1 | Time1 | Program2 | Time2 | Program3 | Time3 | 12345 | Anna | Workshop A | 9am | Workshop B | 9.30am | Workshop C | 10am | 56789 | Brian | Workshop D | 10.30am | NULL | NULL | NULL | NULL | 11223 | Colin | Workshop A | 9am | Workshop C | 10am | NULL | NULL |
Edit: there are lots and lots of program names that could go in column 3, so it would be great if I didn't have to define each program name.