2 table columns into 1 column in report

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

2 table columns into 1 column in report

Author

Topic

motilok
Starting Member

24 Posts

Posted - 2010-10-04 : 10:11:47

I have the following data in my table

Guest_ID
Guest_Name
Driver1
Guest_PickUpDateTime1
Guest_PickUpLocation1
Guest_DropOffDateTime1
Guest_DropOffLocation1
Driver2
Guest_PickUpDateTime2
Guest_PickUpLocation2
Guest_DropOffDateTime2
Guest_DropOffLocation2

I need to create Drivers' schedule report with following columns
Driver ***
Guest_ID
Guest_Name
GuestPickUpDateTime (combined Guest_PickUpDateTime 1 & 2, sorted Asc)
GuestPickUpLocation (combined Guest PickUpLocation 1& 2)
GuestDropOffDateTime (combined Guest_DropOffDateTime 1&2)
GuestDropOffLocation (combined Guest_DropOffLocation 1&2)

PS Not sure how to go about driver, since Driver 1 and Driver 2 can be same or different and reports needs to be for one driver without any extra information.

Example:

Guest_ID - 15, 201, 103
Guest_Name - Jack Smith, Bill Johnson, Amy Slon
Driver1 - Billy, Jim, Eric
Guest_PickUpDateTime1 - 10/15/2010 6am, 10/14/2010 8 am, 10/16/2010 9pm
Guest_PickUpLocation1 - Avalon, PID, Airport
Guest_DropOffDateTime1 - 10/15/2010 6:30 am, 10/14/2010 8:15 am, 10/16/2010 9:45 pm
Guest_DropOffLocation1 - PID, LaQuinta, Marriott
Driver2 - Billy, Billy, Jim
Guest_PickUpDateTime2 - 10/16/2010 9:30am, 10/17/2010 12:15 pm, 10/17/2010 1:00 pm
Guest_PickUpLocation2 - PID, LaQuinta, Marriott
Guest_DropOffDateTime2 - 10/16/2010 10:00am, 10/17/2010 12:30 pm, 10/17/2010 1:15 pm
Guest_DropOffLocation2 - Airport, PID, PID

This is what I want it to be:
Driver: Billy
Guest_ID: 15, 15, 201
Guest_Name: Jack Smith, Jack Smith, Bill Johnson
GuestPickUpDateTime: 10/15/2010 6am, 10/16/2010 9:30 am, 10/17/2010 12:15pm
GuestPickUpLocation: Avalon, LaQuinta, LaQuinta
GuestDropOffDateTime: 10/15/2010 6:30am, 10/16/2010 10:00am,10/17/2010 12:30 pm
GuestDropOffLocation: PID, Airport, PID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 10:39:08

can you post it in correct format please?

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

motilok
Starting Member

24 Posts

Posted - 2010-10-05 : 09:22:27

I have the following data in my table

TripID,Guest_ID,Guest_Name,Driver1,Guest_PickUpDateTime1,Guest_PickUpLocation1,Guest_DropOffDateTime1,Guest_DropOffLocation1,Driver2,Guest_PickUpDateTime2,Guest_PickUpLocation2,Guest_DropOffDateTime2,Guest_DropOffLocation2
148, 35, John Smith, Andrew, 10/15/2010 06:00 am, ERI, 10/15/2010 06:30 am, PID, John, 10/16/2010 1:00 am, PID, 10/16/2010 1:30 am, Marriott

I need Driver's Schedule Looking like this
Driver (Driver1 & Driver2),TripID, Guest_ID,Guest_Name,GuestPickUpDateTime (combined Guest_PickUpDateTime 1 & 2, sorted Asc),GuestPickUpLocation (combined Guest PickUpLocation 1& 2),GuestDropOffDateTime (combined Guest_DropOffDateTime 1&2),GuestDropOffLocation (combined Guest_DropOffLocation 1&2)
ROW1: Andrew, 148, 35, John Smith, 10/15/2010 06:00 am, ERI, 10/15/2010 06:30 am, PID
ROW2: John, 148,35,John Smith, 10/16/2010 01:00 am, PID, 10/16/2010 1:30 am, Marriott

Does this make sence? I really don't know how to put it in a better and more proper format.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 12:21:06

ok. you can do it as follows


SELECT Driver1 AS Driver,
TripID,
Guest_ID,
Guest_Name,
Guest_PickUpDateTime1 AS GuestPickUpDateTime,
Guest_PickUpLocation1 AS GuestPickUpLocation,
Guest_DropOffDateTime1 AS GuestDropOffDateTime,
Guest_DropOffLocation1 AS GuestDropOffLocation
FROM Table
UNION ALL
SELECT Driver2,
TripID,
Guest_ID,
Guest_Name,
Guest_PickUpDateTime2,
Guest_PickUpLocation2,
Guest_DropOffDateTime2,
Guest_DropOffLocation2
FROM Table

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

motilok
Starting Member

24 Posts

Posted - 2010-10-05 : 13:56:29

You are the SQL Wizard! You have no Idea how much I love you!!! I got exactly what I want, my drivers would be happy to see their schedule neat and organized... :)

motilok
Starting Member

24 Posts

Posted - 2010-10-12 : 08:43:36

This is not really a new question, but more of a continuation of my previous question in this post

Previously I had this:

TripID,Guest_ID,Guest_Name,Driver1,Guest_PickUpDateTime1,Guest_PickUpLocation1,Guest_DropOffDateTime1,Guest_DropOffLocation1,Driver2,Guest_PickUpDateTime2,Guest_PickUpLocation2,Guest_DropOffDateTime2,Guest_DropOffLocation2
148, 35, John Smith, Andrew, 10/15/2010 06:00 am, ERI, 10/15/2010 06:30 am, PID, John, 10/16/2010 1:00 am, PID, 10/16/2010 1:30 am, Marriott

Thanks to visakh16, I got this:
Driver (Driver1 & Driver2),TripID, Guest_ID,Guest_Name,GuestPickUpDateTime (combined Guest_PickUpDateTime 1 & 2, sorted Asc),GuestPickUpLocation (combined Guest PickUpLocation 1& 2),GuestDropOffDateTime (combined Guest_DropOffDateTime 1&2),GuestDropOffLocation (combined Guest_DropOffLocation 1&2)
ROW1: Andrew, 148, 35, John Smith, 10/15/2010 06:00 am, ERI, 10/15/2010 06:30 am, PID
ROW2: John, 148,35,John Smith, 10/16/2010 01:00 am, PID, 10/16/2010 1:30 am, Marriott

Now I need to add one more column Status (arriving or leaving), so the data needs to look like this:
Driver (Driver1 & Driver2),TripID, Guest_ID,Guest_Name,GuestPickUpDateTime (combined Guest_PickUpDateTime 1 & 2, sorted Asc),GuestPickUpLocation (combined Guest PickUpLocation 1& 2),GuestDropOffDateTime (combined Guest_DropOffDateTime 1&2),GuestDropOffLocation (combined Guest_DropOffLocation 1&2), Status
ROW1: Andrew, 148, 35, John Smith, 10/15/2010 06:00 am, ERI, 10/15/2010 06:30 am, PID, Arriving
ROW2: John, 148,35,John Smith, 10/16/2010 01:00 am, PID, 10/16/2010 1:30 am, Marriott, Departing

Arriving - if the data was pulled from GuestPickUpDateTime 1 (or any other column with 1)
Departing - if the data was pulled from GuestPickUpDateTime 2 (or any other column with 2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 02:54:08

seems like you just need a derived column more


SELECT Driver1 AS Driver,
TripID,
Guest_ID,
Guest_Name,
Guest_PickUpDateTime1 AS GuestPickUpDateTime,
Guest_PickUpLocation1 AS GuestPickUpLocation,
Guest_DropOffDateTime1 AS GuestDropOffDateTime,
Guest_DropOffLocation1 AS GuestDropOffLocation,
'Arriving' AS Status
FROM Table
UNION ALL
SELECT Driver2,
TripID,
Guest_ID,
Guest_Name,
Guest_PickUpDateTime2,
Guest_PickUpLocation2,
Guest_DropOffDateTime2,
Guest_DropOffLocation2,
'Departing'
FROM Table

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

motilok
Starting Member

24 Posts

Posted - 2010-10-19 : 10:32:14

thank you, you saved me once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 12:42:19

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources