The [DisplayName] column contains the names of passengers associated with a particular [LegID]. The problem is when there are more than 1 passengers on a particular leg I'm get a new row for each passenger name. For example, LegID 1234 is displaying 4 times since there are 4 passengers listed for that leg.
Ideally I would like to add columns for each passenger for a particular leg called pax1, pax2, pax3, pax4, etc. Unfortunately passengers are not given assignments like passenger1, passenger2, etc. Is there a way to do this? FYI, each passenger (user) does have a unique UserID.
Sorry if I wasn't clear. I'm not tying to modify the database. This is strictly for reporting purposes using Report Builder 3.0. I'm just trying to query the existing database to create a report showing each flight leg and associated passengers. I'm a beginner so I don't know a whole lot about more complicated queries.