Help Please - 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

Help Please

Author

Topic

Wilso011
Starting Member

4 Posts

Posted - 2010-08-19 : 12:34:29

I'm running the below query. A HAWB can be associated with multiple MAWBs in this query (up to 4). However, I would like to have the data placed into columns instead of rows. So instead of having the same record showing 2 or more results, I would like to show just 1 result. I think I could do a pivot, but that seems very complicated and wasn't sure if there was an easier option?

Select C.*, case when D.MAWB IS null then ' ' else d.MAWB end as MAWB, case when D.MAWBCons IS null then ' ' else d.MAWBCons end as MAWBCons
, case when D.ConsType IS null then ' ' else d.ConsType end as ConsType, case when D.AgentConsFlag IS null then ' ' else D.AgentConsFlag end as
AgentConsFlag from

(Select A.*, B.Charges, B.Profit from

(select h.hawbnum as HAWB, h.TotalChargableWGT as ChargeableWeight, CONVERT(varchar(10), h.dateshipped, 101) as ShipDate, h.shipr as Shipper, h.consnameaddr as Cons, h.stnnum as Station, h.stndivisioncode as Division,
case when h.stndivisioncode IN ('oceanexpor', 'oceanimpt','poceanimpo') then h.originportcode else h.originairport end as Origin,
case when h.stndivisioncode IN ('oceanexpor', 'oceanimpt','poceanimpo') then h.destportcode else h.destairport end as Dest,
h.cust as Customer, hp.nameaddrcode as Billto, h.freightpaymentmethod as PaymentMethod
from HAWB as h left join NameAddrMstr as n on h.Shipr=n.nameaddrcode
left join HAWBPPDBillTo as hp on h.HAWBNum=hp.hawbnum
left join CountryMaster as c on n.CountryCode=c.countrycode

where h.DateShipped between '1/1/2010' And '7/31/2010' and c.CountryName='Japan'
group by h.HAWBNum, h.DateShipped, h.shipr, h.consnameaddr, h.StnNum, h.StnDivisionCode, h.OriginPortCode, h.OriginAirport, h.DestPortCode, h.DestAirport, h.Cust,
hp.nameaddrcode, h.FreightPaymentMethod, h.TotalChargableWGT)A

left join
(select cd.hawbnum, SUM(case when cd.revexp='r' then cd.proratedamt else 0 end) as Charges,
SUM(case when cd.revexp='r' then cd.proratedamt else 0 end) - SUM(case when cd.revexp='e' then cd.proratedamt else 0 end) as Profit
from ChargeDistribution as cd
where cd.SpareDate1 between '1/1/10' and '7/31/10' and cd.PostedToAcctg='y'
group by cd.hawbnum)B on A.hawb=b.hawbnum)C

left join
(select d.DocNum1, m.MAWBNum as MAWB, m.consnameaddr as MAWBCons, m.ConsTypeCode as ConsType, m.AgentConsFlag as AgentConsFlag from DocumentDocument as d left join MAWB as m on d.DocNum2=m.mawbnum where d.Doc2DocType='mawb'
)D on C.HAWB=d.docnum1

union all

Select C.*, case when D.MAWB IS null then ' ' else d.MAWB end as MAWB, case when D.MAWBCons IS null then ' ' else d.MAWBCons end as MAWBCons
, case when D.ConsType IS null then ' ' else d.ConsType end as ConsType, case when D.AgentConsFlag IS null then ' ' else D.AgentConsFlag end as
AgentConsFlag from

(Select A.*, B.Charges, B.Profit from
(select h.hawbnum as HAWB, h.TotalChargableWGT as ChargeableWeight, CONVERT(varchar(10), h.dateshipped, 101) as ShipDate, h.shipr as Shipper, h.consnameaddr as Cons, h.stnnum as Station, h.stndivisioncode as Division,
case when h.stndivisioncode IN ('oceanexpor', 'oceanimpt','poceanimpo') then h.originportcode else h.originairport end as Origin,
case when h.stndivisioncode IN ('oceanexpor', 'oceanimpt','poceanimpo') then h.destportcode else h.destairport end as Dest,
h.cust as Customer, hp.nameaddrcode as Billto, h.freightpaymentmethod as PaymentMethod
from HAWB as h left join NameAddrMstr as n on h.consnameaddr=n.nameaddrcode
left join HAWBPPDBillTo as hp on h.HAWBNum=hp.hawbnum
left join CountryMaster as c on n.CountryCode=c.countrycode

where h.DateShipped between '1/1/2010' And '7/31/2010' and c.CountryName='Japan'
group by h.HAWBNum, h.DateShipped, h.shipr, h.consnameaddr, h.StnNum, h.StnDivisionCode, h.OriginPortCode, h.OriginAirport, h.DestPortCode, h.DestAirport, h.Cust,
hp.nameaddrcode, h.FreightPaymentMethod, h.TotalChargableWGT)A

left join
(select cd.hawbnum, SUM(case when cd.revexp='r' then cd.proratedamt else 0 end) as Charges,
SUM(case when cd.revexp='r' then cd.proratedamt else 0 end) - SUM(case when cd.revexp='e' then cd.proratedamt else 0 end) as Profit
from ChargeDistribution as cd
where cd.SpareDate1 between '1/1/10' and '7/31/10' and cd.PostedToAcctg='y'
group by cd.hawbnum)B on A.hawb=b.hawbnum)C

left join
(select d.DocNum1, m.MAWBNum as MAWB, m.consnameaddr as MAWBCons, m.ConsTypeCode as ConsType, m.AgentConsFlag as AgentConsFlag from DocumentDocument as d left join MAWB as m on d.DocNum2=m.mawbnum where d.Doc2DocType='mawb'
)D on C.HAWB=d.docnum1

order by c.hawb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:16:53

can you show the data from tables and then explain what you want? its quite difficult to understand your requirement by looking at query

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

Wilso011
Starting Member

4 Posts

Posted - 2010-08-19 : 14:33:04

This is a limited sample of my results:

HAWB Weight Profit MAWB MAWBCons
5258294 205 100.00 34407644 BLUELINED5
5258294 205 100.00 16045849112 AOTJAPAN
5258294 205 100.00 023798628705259

I would somehow like to have the return like this:
Hawb Weight Profit Mawb MAWBCons MAWB2 MAWBCons
5258294 205 100.00 34407644 Bluelined5 16045949112 AOTJAPAN

Only have 1 result for each HAWB even though I may have multiple MAWBs. Hope this clarifies. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 14:49:10

So what happened to third value?
023798628705259

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

Wilso011
Starting Member

4 Posts

Posted - 2010-08-19 : 14:52:38

It wouldn't fit properly in the window provided, so I just left it off the example. However, I would need to return all 3 values/results on 1 line.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 14:54:04

ok. As value of single field or three different fields?

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

Wilso011
Starting Member

4 Posts

Posted - 2010-08-19 : 15:11:13

1 result with 3 (potentially more) different fields for MAWB.

Hawb Profit Mawb MawbCons Mawb2 MawbCons Mawb3
5258294 100.00 34407644 xyz 16045849112 xyz 023798628705259

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 15:13:16

then what you need is dynamic pivot

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources