Single Customer View

[MIS2010]

Hello Alll, can someone please assist me with this.

I need to create/output Single customer view file from a view. I am not to sure how to do it.

Booking Client Booking Ref Booking Country Code Booking Dep Date compname Component Counter comptype CountryCode GBPCost price
107676 L001271 Myanmar 18-06-2010 Accommodation 1 Accommodation United States Of America 211.6 264.4991
107676 L001271 Myanmar 18-06-2010 Flight 1 Flight Portugal 0 0
107676 L001271 Myanmar 18-06-2010 Flight 1 Flight Portugal 425 425
107676 L001271 Myanmar 18-06-2010 Flight 1 Flight Brazil 0 0
107676 L001271 Myanmar 18-06-2010 Flight 1 Flight United Kingdom 0 0
107676 L001271 Myanmar 18-06-2010 APC 1 Extra - All Other Myanmar 0 2
107676 L001271 Myanmar 18-06-2010 WEXAS TRAVEL FOUNDATION DONATION 1 Extra - All Other Myanmar 2 2
107676 L001271 Myanmar 18-06-2010 Airport Tax 1 Extra - Airport Tax Portugal 309 309

End result i am looking for is
Booking Client Booking Ref Booking Country Code Booking Dep Date GBPCost Price Accomodation Flight APC Airport Tax
107676 L001271 Myanmar 18-06-2010 947.6 1002.4991 1 1 1 1


Apologies for data being all over the place

Any assitance will be help a lot

Kind Regards

 

[Crowley16]

have you ever come across the "group by" clause and aggregate functions?

something like:

select
SomeId,
SomeName,
sum(Value1) as SumVal1,
sum(Value2) as SumVal2
from
SomeTable
group by
SomeId,
SomeName

You'll probably need to play around a bit with case statements to clarify the flight/accommodation/tax columns, but you can build the sum around a case statement to do this. e.g.

avg(
case
when field1 = 'flight' then 1
else 0
end
)

--------------------
Procrastinate Now!