How to separate one column's data into multiple columns?

Question

Here's my situation : I have a table that has large amounts of records, I need to pull out a number of these records for each name in the database, note that TOP will not work for my use case. My end user wants the report formatted in such a way that each user shows up only once, and up to 3 different dates are shown for the user.

Table format

AutoID	Enum	TNum	Date	Comments
1	25	18	2/2/22
2	25	18	1/2/21	Blah
3	18	18	1/2/21
4	18	18	1/2/20
5	25	17	1/2/22
6	25	17	1/2/20

Now the Enum and TNum fields are fk with other tables, I have created a join that pulls the correct information from the other tables. In the end my query provides this output

RecordID	Training	CompletedDate			FirstName	LastName	Location
2821	MaP	1/1/21			David	Simpson	123 Sesame St.	2822	1/2/22		Fuller		MaP			Dough		GHI		David	123 Sesame St.	2825	1/1/20		Simpson

The two "Blank fields" represent information that is pulled and may or may not be needed in some future report.

So to my question : How do I manage to get a report, with this query's pull to look like this:

Place	LastName	FirstName	Training	FirstCuttoff	Secondcutoff	ThirdCutoff	Comments
123 Sesame St.	David	Simpson	MaP	1/1/20	1/1/21
123 Sesame St.	John	Dough	MaP			1/1/22

I was originally planning on joining my query to itself using where clauses. But when I tried that it just added two extra columns of the same date. In addition it is possible that each record is not identical; locations may be different but since the report needs the most recent location and the name of the trainee. In addition, to add more complexity, there are a number of people in the company with effectively the same name as far as the database is concerned, so rejoining on the name is out. I did pull the Enum in my query, I can join on that if needed.

Is there an easier way to do this, or do I need to sort out a multiple self-joining query?

Answer 1

I have a project I am working on where I am going to have to do this. Some of the suggestions I received were to use a Pivot query. It wouldn't work in my case but it might for yours. Here is a good example

Pivot Columns