Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: Pivot, Case, or ???

    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!
    Last edited by stharish; 01-05-12 at 21:34.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    7
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •