Cannot get correct recordset. - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Cannot get correct recordset.

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-19 : 07:52:25

Hi,
Below are the Table structures and some table screenshots of 2 tables in my DB. I need to return a recordset to an ASP page that is basically a summary of a booking, based on the BookingId that the user enters. What I want is this:

BookingId - JourneyTime - 'First Pickup Location' - 'Last DropOff Location'

The Passenger pickup and dropoff location details are referenced by the Pax_Order column in the Bookings_Details table and correspond to BookingId which is Foreign Key to Bookings table.

I cannot figure a way to do this when my table that complies to 3rd normal form (I think). Can anyone advise me on a way to get the resultset please.

CREATE TABLE [dbo].[Bookings] (
[BookingId] [bigint] IDENTITY (50000, 1) NOT FOR REPLICATION NOT NULL ,
[BookingStatus] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DriverNo] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateTimeBooked] [datetime] NOT NULL ,
[CustomerId] [int] NOT NULL ,
[PaymentType] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JourneyDate] [datetime] NOT NULL ,
[JourneyTime] [datetime] NOT NULL ,
[JourneyHour] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[JourneyMinute] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VehicleType] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Trailer] [tinyint] NULL ,
[NoOfPax] [int] NOT NULL ,
[PickupType] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FlightTrainNo] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WaitandReturn] [tinyint] NULL ,
[WaitandReturnAddress] [varchar] (130) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EstWaitandReturnTime] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActualWaitandReturnTime] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BookingRequirements] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LinkedReference] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SplitCostID] [bigint] NULL ,
[AdditionalWaitingTime] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Updated] [tinyint] NULL ,
[UpdateDateTime] [datetime] NULL ,
[System_Date] [datetime] NULL

CREATE TABLE [dbo].[Bookings_Details] (
[DetailsID] [bigint] IDENTITY (1, 1) NOT NULL ,
[BookingId] [bigint] NOT NULL ,
[Customerid] [int] NULL ,
[Pax_Order] [int] NULL ,
[PaxFname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaxSname] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaxTel1] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PaxTel2] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pickup] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dropoff] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-19 : 08:29:58

Can you post some sample data?

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-19 : 08:39:43

Hi,
what do you mean by sample data, do the screenshots not show this?

Pat.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-19 : 08:41:48

quote:
Originally posted by patshaw

Hi,
what do you mean by sample data, do the screenshots not show this?

Pat.

i can't see the screen shot at all

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-19 : 08:56:35

Yeah I can't see the SS's either.

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-19 : 09:10:41

Here we are, sorry about the formating.

Booking table:

BookingId BookingStatus DriverNo DateTimeBooked CustomerId PaymentType JourneyDate JourneyTime JourneyHour JourneyMinute VehicleType Trailer NoOfPax PickupType FlightTrainNo WaitandReturn WaitandReturnAddress EstWaitandReturnTime ActualWaitandReturnTime BookingRequirements LinkedReference SplitCostID AdditionalWaitingTime Updated UpdateDateTime System_Date
-------------------- --------------- -------- ------------------------------------------------------ ----------- ------------ ------------------------------------------------------ ------------------------------------------- ----------- ------------- ------------------ ------- ----------- ------------------------- ------------------------------ ------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------------- ----------------------- ------------------------------------------------------------------------------------------------------------------------ --------------- -------------------- --------------------- ------- ------------------------------------------------------ ------------------------------------------------------
50024 Pending NULL 2006-07-01 22:57:13.373 10029 Credit Card 2006-07-04 00:00:00.000 22:20:00.000 22 20 Executive Saloon 0 2 Airport BA1611 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-04 00:00:00.000
50028 Pending NULL 2006-07-02 00:05:29.403 10041 Credit Card 2006-07-11 00:00:00.000 20:20:00.000 20 20 Executive Saloon 0 2 Address N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-11 00:00:00.000
50029 Pending NULL 2006-07-02 00:21:31.093 10041 Credit Card 2006-07-19 00:00:00.000 16:25:00.000 16 25 Executive Saloon 0 2 Address N/A 0 N/A N/A NULL 50028 NULL NULL 0 NULL 2006-07-19 00:00:00.000
50030 Pending NULL 2006-07-02 00:39:45.547 10041 Credit Card 2006-07-21 00:00:00.000 10:00:00.000 10 00 Executive Saloon 0 3 Address N/A 1 N/A 2 hours N/A NULL N/A NULL NULL 0 NULL 2006-07-21 00:00:00.000
50031 Pending NULL 2006-07-02 00:43:26.250 10041 Credit Card 2006-07-25 00:00:00.000 14:25:00.000 14 25 Executive Saloon 0 2 Airport BD594 0 N/A N/A NULL 50030 NULL NULL 0 NULL 2006-07-25 00:00:00.000
50033 Pending NULL 2006-07-05 21:53:19.607 10029 Credit Card 2006-07-12 00:00:00.000 12:20:00.000 12 20 Executive Saloon 0 2 Airport BD598 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-12 00:00:00.000
50034 Pending NULL 2006-07-05 22:00:35.843 10029 Credit Card 2006-07-20 00:00:00.000 10:10:00.000 10 10 Executive Saloon 0 2 Address N/A 0 N/A N/A NULL 50033 NULL NULL 0 NULL 2006-07-20 00:00:00.000
50035 Pending NULL 2006-07-05 22:03:53.123 10029 Credit Card 2006-07-15 00:00:00.000 20:25:00.000 20 25 Executive Saloon 0 3 Address N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-15 00:00:00.000
50036 Pending NULL 2006-07-09 00:10:59.497 10029 Credit Card 2006-07-24 00:00:00.000 01:00:00.000 01 00 Executive Saloon 0 1 Function 1-Way-RETURN N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-23 00:00:00.000
50037 Pending NULL 2006-07-09 00:24:03.390 10029 Credit Card 2006-07-26 00:00:00.000 18:45:00.000 18 45 Executive Saloon 0 1 Function 1-Way-OUT N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-26 00:00:00.000
50038 Pending NULL 2006-07-09 00:26:47.013 10029 Credit Card 2006-07-29 00:00:00.000 14:20:00.000 14 20 Executive Saloon 0 1 Airport BA1398 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-29 00:00:00.000
50039 Pending NULL 2006-07-09 00:38:31.420 10029 Credit Card 2006-07-19 00:00:00.000 02:00:00.000 02 00 Executive Saloon 0 1 Function 1-Way-RETURN N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-18 00:00:00.000
50040 Pending NULL 2006-07-09 00:51:00.700 10029 Cash 2006-08-15 00:00:00.000 00:30:00.000 00 30 Executive Saloon 0 1 Function 1-Way-RETURN N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-08-14 00:00:00.000
50041 Pending NULL 2006-07-09 00:52:27.967 10029 Cash 2006-09-21 00:00:00.000 23:30:00.000 23 30 Executive Saloon 0 1 Function 1-Way-RETURN N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-09-21 00:00:00.000
50042 Pending NULL 2006-07-09 00:54:53.530 10029 Credit Card 2006-08-25 00:00:00.000 15:10:00.000 15 10 Executive Saloon 0 1 Address N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-08-25 00:00:00.000
50043 Pending NULL 2006-07-16 00:24:17.043 10029 Credit Card 2006-07-20 00:00:00.000 18:30:00.000 18 30 Executive Saloon 0 1 Function 1-Way-OUT N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-20 00:00:00.000
50044 Pending NULL 2006-07-16 00:44:32.780 10029 Credit Card 2006-07-18 00:00:00.000 19:20:00.000 19 20 Executive Saloon 0 1 Function 1-Way-OUT N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-18 00:00:00.000
50045 Pending NULL 2006-07-16 01:00:41.403 10029 Credit Card 2006-07-26 00:00:00.000 20:00:00.000 20 00 Executive Saloon 0 1 Function 1-Way-OUT N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-07-26 00:00:00.000
50046 Pending NULL 2006-07-16 01:03:00.157 10029 Credit Card 2006-07-26 00:00:00.000 23:30:00.000 23 30 Executive Saloon 0 1 Function 1-Way-RETURN N/A 0 N/A N/A NULL 50045 NULL NULL 0 NULL 2006-07-26 00:00:00.000
50047 Pending NULL 2006-07-18 22:43:40.373 10029 Cash 2006-07-20 00:00:00.000 22:20:00.000 22 20 Executive Saloon 0 2 Airport BA1498 0 N/A N/A NULL Please instruct driver to bring refreshments. N/A NULL NULL 0 NULL 2006-07-20 00:00:00.000
50048 Pending NULL 2006-07-18 22:51:02.183 10029 Credit Card 2006-09-05 00:00:00.000 19:00:00.000 19 00 Executive Saloon 0 2 Function 1-Way-OUT N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-09-05 00:00:00.000
50049 Pending NULL 2006-07-18 22:59:11.297 10029 Credit Card 2006-09-21 00:00:00.000 15:10:00.000 15 10 Executive Saloon 0 1 Address N/A 0 N/A N/A NULL N/A NULL NULL 0 NULL 2006-09-21 00:00:00.000

(22 row(s) affected)

Booking_Details:
DetailsID BookingId Customerid Pax_Order PaxFname PaxSname PaxTel1 PaxTel2 Pickup Dropoff
-------------------- -------------------- ----------- ----------- ------------------------- ------------------------- --------------- --------------- ------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------
1 50024 10029 1 Patrick Shaw 01253728936 07861512378 Manchester Airport, Manchester, M90 1QX 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
2 50024 10029 2 Deborah Shaw 01253728936 07861512378 Manchester Airport, Manchester, M90 1QX 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
3 50028 10041 1 David Short 01253613489 07970894336 Dalmeny Hotel,19-33 South Promenade,,St Annes,Lancashire FY8 1LX. tel:01253 712236 Birmingham International Airport, Birmingham, B26 3QJ
4 50028 10041 2 Sarah Stuart 01772643552 07970894336 Dalmeny Hotel,19-33 South Promenade,,St Annes,Lancashire FY8 1LX. tel:01253 712236 Heathrow Airport, Hayes, Middlesex, UB3 5AP
5 50029 10041 1 David Short 01253613489 07970894336 Heathrow Airport, Hayes, Middlesex, UB3 5AP Chadwick Hotel, South Promenade,Lytham,St.Annes,Lancashire FY8 1NP. tel:
6 50029 10041 2 Sarah Stuart 01253613489 07970894336 Heathrow Airport, Hayes, Middlesex, UB3 5AP Chadwick Hotel, South Promenade,Lytham,St.Annes,Lancashire FY8 1NP. tel:
11 50030 10041 1 David Short 01253613489 07970894336 100 Crofter Avenue, Blackpool, Fylde, Lancashire FY42RT Manchester Airport, Manchester, M90 1QX
12 50030 10041 2 Peter Haynes 01253722435 07970894336 2, Bryning Lane, Warton, Preston, PR21ER Manchester Airport, Manchester, M90 1QX
13 50030 10041 3 Sue Hallcross 01772345117 07970894336 W23, BAe Systems, Warton Manchester Airport, Manchester, M90 1QX
14 50031 10041 1 David Short 01253613489 07970894336 Manchester Airport, Manchester, M90 1QX W23, BAe Systems, Warton
15 50031 10041 2 Sue Hallcross 01253613489 07970894336 Manchester Airport, Manchester, M90 1QX W23, BAe Systems, Warton
17 50033 10029 1 Patrick Shaw 01253728926 07861512378 Manchester Airport, Manchester, M90 1QX 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
18 50033 10029 2 Deborah Shaw 01253728926 07861512378 Manchester Airport, Manchester, M90 1QX 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
19 50034 10029 1 Patrick Shaw 01253728926 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Liverpool John Lennon Airport, Liverpool, L24 1YD
20 50034 10029 2 Deborah Shaw 01253728926 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Liverpool John Lennon Airport, Liverpool, L24 1YD
21 50035 10029 1 Patrick Shaw 01253728926 07861512378 Clifton Arms Hotel, West Beach,,Lytham,Lancashire FY8 5QJ. tel:01253 793898 Manchester Airport, Manchester, M90 1QX
22 50035 10029 2 Deborah Shaw 01253728926 07861512378 Clifton Arms Hotel, West Beach,,Lytham,Lancashire FY8 5QJ. tel:01253 793898 Manchester Airport, Manchester, M90 1QX
23 50035 10029 3 Keira Shaw 01253728926 07861512378 Clifton Arms Hotel, West Beach,,Lytham,Lancashire FY8 5QJ. tel:01253 793898 Manchester Airport, Manchester, M90 1QX
24 50036 10029 1 Patrick Shaw 01253728926 07861512378 Barton Grange Hotel, Garstang Road,Broughton,Preston,Lancashire PR3 5AA. tel:01772 862551 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
25 50037 10029 1 Patrick Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Hilton Hotel Blackpool, North Promenade,,Blackpool,Lancashire FY1 2JQ. tel:01253 623434
26 50038 10029 1 Patrick Shaw 01253728936 07861512378 Manchester Airport, Manchester, M90 1QX Clifton Arms Hotel, West Beach,,Lytham,Lancashire FY8 5QJ. tel:01253 793898
27 50039 10029 1 Patrick Shaw 01253728936 07861512378 Barton Grange Hotel, Garstang Road,Broughton,Preston,Lancashire PR3 5AA. tel:01772 862551 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
28 50040 10029 1 Patrick Shaw 01253728936 07861512378 Imperial Hotel Blackpool, North Promenade,,Blackpool,Lancashire FY1 2HB. tel: 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
29 50041 10029 1 Patrick Shaw 01253728936 07861512378 Imperial Hotel Blackpool, North Promenade,,Blackpool,Lancashire FY1 2HB. tel: 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
30 50042 10029 1 Patrick Shaw 01253728936 07861512378 Clifton Arms Hotel, West Beach,,Lytham,Lancashire FY8 5QJ. tel:01253 793898 Gatwick Airport, West Sussex, RH6 0NP
31 50043 10029 1 Patrick Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Barton Grange Hotel, Garstang Road,Broughton,Preston,Lancashire PR3 5AA. tel:01772 862551
32 50044 10029 1 Patrick Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ De Vere Herons Reach(Blackpool), East Park Drive,,Blackpool,Lancashire FY3 8LL. tel:
33 50045 10029 1 Patrick Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Hilton Hotel Blackpool, North Promenade,,Blackpool,Lancashire FY1 2JQ. tel:01253 623434
34 50046 10029 1 Patrick Shaw 01253728936 07861512378 Hilton Hotel Blackpool, North Promenade,,Blackpool,Lancashire FY1 2JQ. tel:01253 623434 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ
35 50047 10029 1 Patrick Shaw 01253728936 07861512378 Manchester Airport, Manchester, M90 1QX Dalmeny Hotel,19-33 South Promenade,,St Annes,Lancashire FY8 1LX. tel:01253 712236
36 50047 10029 2 Deborah Shaw 01253728936 07861512378 Manchester Airport, Manchester, M90 1QX Dalmeny Hotel,19-33 South Promenade,,St Annes,Lancashire FY8 1LX. tel:01253 712236
37 50048 10029 1 Patrick Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Dalmeny Hotel,19-33 South Promenade,,St Annes,Lancashire FY8 1LX. tel:01253 712236
38 50048 10029 2 Deborah Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Dalmeny Hotel,19-33 South Promenade,,St Annes,Lancashire FY8 1LX. tel:01253 712236
39 50049 10029 1 Patrick Shaw 01253728936 07861512378 33a Clymont Road, Lytham St. Annes, Lancashire FY81LQ Manchester Airport, Manchester, M90 1QX

(34 row(s) affected)

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-19 : 10:10:51

How does this sound to you all:

If I create a 3rd de-normalised table that contains the details that I need for this recordset, I could Insert the data to this table when I do the Insert to the other 2 tables.

This sound too elaborate for my liking. Is there no way this can be done by querying the existing two tables?

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-20 : 02:55:30

Hi all,

Can no-one else offer any further help or advice on this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 03:04:16

I think you have too much information here for those trying to help to comprehend. Can you post only the relevant table structure, some sample data and the result that you want ?

Also refer to here http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Thanks

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-20 : 04:02:01

Sorry if I over complicated this, but my question really is quite simple.

From the two tables I need to return a resultset that gives me:

1. The BookingId (PK in 'Bookings' table and FK in 'Bookings_Details' table)
2. The JourneyTime (from 'Bookings' table)
3. The First PickUp location ('PickUp' Column in Bookings_Details table where Pax_Order = 1)
4. The Last DropOff location ('DropOff' Column in Bookings_Details table where Pax_Order = Maximum Pax_Order - this could be any number between 1 and 6)

The parameter for the query is the BookingId that the user enters.

Hope this makes my question clearer.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 04:21:11

try this


select b.BookingId, b.JourneyTime, d.FirstPickup, d.LastDropOff
from   Bookings b
inner join
(
    select BookingId, 
           FirstPickUp = (select top 1 PickUp  from Bookings_Details x where x.BookingId = bd.BookingId order by Pax_Order),
           LastDropOff = (select top 1 DropOff from Bookings_Details x where x.BookingId = bd.BookingId order by Pax_Order desc) 
    from   Bookings_Details bd
    group by BookingId
) d
on b.BookingId = d.BookingId

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-20 : 04:41:48

Thanks very much Khtan,

I currently cannot get at the database as it is on my local machine at home, but as soon as I get home I will test it. It looks pretty much spot-on to me though. Thankyou.

Pat.

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-21 : 04:06:12

Hi,

I now need to modify the SQL khtan gave me to grab the surnames of the passengers, concatenate them, and display them as 1 column.

This is easy enough but for the fact that a booking can have between 1 and 6 passengers. So, for example if a booking only has 3 passengers then my query returns only NULL without the 1st and 2nd passengers name. In this scenario, I would like to return these passengers names only and disregard all NULL values.

How can I achieve this?

select b.BookingId, b.JourneyTime, d.FirstPickup, d.LastDropOff,
d.Pax1+'|'+d.Pax2+'|'+d.Pax3+'|'+d.Pax4+'|'+d.Pax5+'|'+d.Pax6 [Passengers]
from Bookings b
inner join
(
select BookingId,
FirstPickUp = (select top 1 PickUp from Bookings_Details x where x.BookingId = bd.BookingId order by Pax_Order),
LastDropOff = (select top 1 DropOff from Bookings_Details x where x.BookingId = bd.BookingId order by Pax_Order desc),
Pax1 = (SELECT PaxSname from Bookings_Details x where x.BookingId = bd.BookingId AND Pax_Order = 1),
Pax2 = (SELECT PaxSname from Bookings_Details x where x.BookingId = bd.BookingId AND Pax_Order = 2),
Pax3 = (SELECT PaxSname from Bookings_Details x where x.BookingId = bd.BookingId AND Pax_Order = 3),
Pax4 = (SELECT PaxSname from Bookings_Details x where x.BookingId = bd.BookingId AND Pax_Order = 4),
Pax5 = (SELECT PaxSname from Bookings_Details x where x.BookingId = bd.BookingId AND Pax_Order = 5),
Pax6 = (SELECT PaxSname from Bookings_Details x where x.BookingId = bd.BookingId AND Pax_Order = 6),
from Bookings_Details bd
group by BookingId
) d
on b.BookingId = d.BookingId

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 05:20:18

change to

d.Pax1 + 
isnull('|' + d.Pax2, '') + 
isnull('|' + d.Pax3, '') + 
isnull('|' + d.Pax4, '') + 
isnull('|' + d.Pax5, '') + 
isnull('|' + d.Pax6, '') [Passengers]

patshaw
Posting Yak Master

177 Posts

Posted - 2006-07-21 : 05:40:30

Thanks again khtan.

gugarc
Starting Member

17 Posts

Posted - 2006-07-21 : 09:13:52

Take a look in the books online, search for PIVOT TABLE - there you can find detailed information on how to transform lines (records/tuples) in columns (atributes).

it may help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources