Hello,

(SQL Server 2008 R2)

I have a list of flight legs with columns:

[Date] [Trip#] [LegID] [Account] [DepartureTime] [Origin] [Dest] [ArrivalTime] [DisplayName]

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.

Please help!

The data model looks OK to me. Don't touch it. You'll make things worse.

People come to this forum to ask how they can deal with a data model like the one you seem to want to achieve. They get the advice to normalise it to your current data model.

Don't touch it.

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.

Hope this helps.

Thanks

Would it not be more appropriate to have your report grouped so that all passengers information was shown below each individual flight leg, rather than columnar?

To do this write a query joining the flight leg and passenger tables and in the report builder at a section/grouping by flight. The wizard should be able to help you perform this