Grouping Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping Question

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2012-01-05 : 16:40:51

Hello,

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. 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!

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-01-05 : 17:24:08

Look into using pivot with a sql command to have the number of columns be variable.

Likes to run, hates the runs!

stharish
Starting Member

43 Posts

Posted - 2012-01-05 : 18:26:02

Would you be able to help me by providing an example using the pivot method?

Would like to see 4 (max) new columns called Passenger1, Passenger2, Passenger3, Passenger4 for each distinct flight leg (LEGID)

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-06 : 14:18:56

[code]
SELECT [Date], [Trip#], [LegID], [Account], [DepartureTime], [Origin], [Dest], [ArrivalTime],
MAX(CASE WHEN Rn =1 THEN DisplayName END) AS Passenger1,
MAX(CASE WHEN Rn =2 THEN DisplayName END) AS Passenger2,
MAX(CASE WHEN Rn =3 THEN DisplayName END) AS Passenger3,
MAX(CASE WHEN Rn =4 THEN DisplayName END) AS Passenger4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY DisplayName) AS Seq, *
FROM YourTable
)t
GROUP BY [Date], [Trip#], [LegID], [Account], [DepartureTime], [Origin], [Dest], [ArrivalTime]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-06 : 20:01:46

Got an error using that coding. FYI I left out the GROUP BY part.

Msg 102, Level 15, State 1, Line 39
Incorrect syntax near '13'.

Here's what I have. Can you let me know what I did wrong? I wasn't sure how this part fit:

FROM YourTable
)t

SELECT

Convert(Date,Legs.DepartureTime) as [Date] --Convert datetime to date
,Trips.TripNumber as [Trip#]
,Legs.LegID as [LegID]
,Accounts.AccountName as [Account]
,Contracts.ContractType as [AccountType]
,Legs.DepartureTime as [DepartureTime]
,Origin.AirportICAOCode as [Origin]
,Destination.AirportICAOCode as [Destination]
,Legs.ArrivalTime as [ArrivalTime]
,Pax.DisplayName as [PassengerName]

,MAX(CASE WHEN Rn =1 THEN DisplayName END) AS Passenger1,
MAX(CASE WHEN Rn =2 THEN DisplayName END) AS Passenger2,
MAX(CASE WHEN Rn =3 THEN DisplayName END) AS Passenger3,
MAX(CASE WHEN Rn =4 THEN DisplayName END) AS Passenger4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY DisplayName) AS Seq, *

FROM tm_Legs as Legs

INNER JOIN tl_Airports as Origin --Join Airports table for Origin
ON Origin.AirportID=Legs.OriginAirportID
INNER JOIN tl_Airports as Destination --Join Airports table for Destination
ON Destination.AirportID=Legs.DestinationAirportID
INNER JOIN tm_Trips as Trips --Join Trips Table
ON Trips.TripID=Legs.TripID
INNER JOIN tm_Accounts as Accounts --Join Accounts Table
ON Accounts.AccountID=Trips.AccountID
INNER JOIN tm_Contracts as Contracts --Join Contracts Table
ON Contracts.ContractID=Trips.ContractID
INNER JOIN tr_EntityUsers as EU --Join leg users (passengers) with legs table
ON Legs.LegID=EU.EntityKeyID
LEFT OUTER JOIN tm_Users as Pax --Join leg users with Users table
ON Pax.UserID=EU.UserID

WHERE Legs.StatusTypeID <>13 --Filter out cancelled legs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 00:44:57

[code]
SELECT [Date], [Trip#], [LegID], [Account],[AccountType], [DepartureTime], [Origin], [Dest], [ArrivalTime]
,MAX(CASE WHEN Rn =1 THEN PassengerName END) AS Passenger1,
MAX(CASE WHEN Rn =2 THEN PassengerName END) AS Passenger2,
MAX(CASE WHEN Rn =3 THEN PassengerName END) AS Passenger3,
MAX(CASE WHEN Rn =4 THEN PassengerName END) AS Passenger4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY DisplayName) AS Seq,
Convert(Date,Legs.DepartureTime) as [Date] --Convert datetime to date
,Trips.TripNumber as [Trip#]
,Legs.LegID as [LegID]
,Accounts.AccountName as [Account]
,Contracts.ContractType as [AccountType]
,Legs.DepartureTime as [DepartureTime]
,Origin.AirportICAOCode as [Origin]
,Destination.AirportICAOCode as [Destination]
,Legs.ArrivalTime as [ArrivalTime]
,Pax.DisplayName as [PassengerName]

FROM tm_Legs as Legs

INNER JOIN tl_Airports as Origin --Join Airports table for Origin
ON Origin.AirportID=Legs.OriginAirportID
INNER JOIN tl_Airports as Destination --Join Airports table for Destination
ON Destination.AirportID=Legs.DestinationAirportID
INNER JOIN tm_Trips as Trips --Join Trips Table
ON Trips.TripID=Legs.TripID
INNER JOIN tm_Accounts as Accounts --Join Accounts Table
ON Accounts.AccountID=Trips.AccountID
INNER JOIN tm_Contracts as Contracts --Join Contracts Table
ON Contracts.ContractID=Trips.ContractID
INNER JOIN tr_EntityUsers as EU --Join leg users (passengers) with legs table
ON Legs.LegID=EU.EntityKeyID
LEFT OUTER JOIN tm_Users as Pax --Join leg users with Users table
ON Pax.UserID=EU.UserID

WHERE Legs.StatusTypeID <>13 --Filter out cancelled legs
)t
GROUP BY [Date], [Trip#], [LegID], [Account],[AccountType], [DepartureTime], [Origin], [Dest], [ArrivalTime]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-07 : 01:07:52

Thanks for the reply! I think I'm getting close but getting the following:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Rn'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Rn'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Rn'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'Rn'.

I changed Rn to ROW_NUMBER (just a guess) but that didn't help. Also, I cleared another error by changing [Dest] to [Destination]

Any ideas? Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-07 : 02:10:48

[code]
SELECT [Date], [Trip#], [LegID], [Account],[AccountType], [DepartureTime], [Origin], [Dest], [ArrivalTime]
,MAX(CASE WHEN Rn =1 THEN PassengerName END) AS Passenger1,
MAX(CASE WHEN Rn =2 THEN PassengerName END) AS Passenger2,
MAX(CASE WHEN Rn =3 THEN PassengerName END) AS Passenger3,
MAX(CASE WHEN Rn =4 THEN PassengerName END) AS Passenger4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY LegID ORDER BY DisplayName) AS Rn,
Convert(Date,Legs.DepartureTime) as [Date] --Convert datetime to date
,Trips.TripNumber as [Trip#]
,Legs.LegID as [LegID]
,Accounts.AccountName as [Account]
,Contracts.ContractType as [AccountType]
,Legs.DepartureTime as [DepartureTime]
,Origin.AirportICAOCode as [Origin]
,Destination.AirportICAOCode as [Destination]
,Legs.ArrivalTime as [ArrivalTime]
,Pax.DisplayName as [PassengerName]

FROM tm_Legs as Legs

INNER JOIN tl_Airports as Origin --Join Airports table for Origin
ON Origin.AirportID=Legs.OriginAirportID
INNER JOIN tl_Airports as Destination --Join Airports table for Destination
ON Destination.AirportID=Legs.DestinationAirportID
INNER JOIN tm_Trips as Trips --Join Trips Table
ON Trips.TripID=Legs.TripID
INNER JOIN tm_Accounts as Accounts --Join Accounts Table
ON Accounts.AccountID=Trips.AccountID
INNER JOIN tm_Contracts as Contracts --Join Contracts Table
ON Contracts.ContractID=Trips.ContractID
INNER JOIN tr_EntityUsers as EU --Join leg users (passengers) with legs table
ON Legs.LegID=EU.EntityKeyID
LEFT OUTER JOIN tm_Users as Pax --Join leg users with Users table
ON Pax.UserID=EU.UserID

WHERE Legs.StatusTypeID <>13 --Filter out cancelled legs
)t
GROUP BY [Date], [Trip#], [LegID], [Account],[AccountType], [DepartureTime], [Origin], [Dest], [ArrivalTime]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-07 : 13:09:03

Thank you so much! This works great! One more question if you don't mind...

I am able to determine the Lead (primary) passenger for each leg by joining the Users table in this manner:

LEFT OUTER JOIN tm_Users as LeadPax
on EU.UserID=LeadPax.UserID and EU.ContextFlags=1

(Please advise if you think this should be an inner join??)

Ideally I would like to replace the [Passenger1] column with [LeadPax]. If not obvious, the lead passenger is defined within the EntityUsers table (EU) where ContextFlags=1.

I tried adding the join but received an error about DisplayName being ambiguous.

Thanks again for your help. You're examples have been very helpful as well as educational.

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 02:35:14

Whats the relationship of tm_users table with tr_EntityUsers ? is it 1 to 1 ie whether it will have match for every record? else INNER JOIN will ignore those records without match in users (i hope it will have match always as I assume its master table for users)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-08 : 11:05:52

I'm pretty sure it should actually be inner join.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-08 : 11:29:32

then you're fine to go with it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources