Below is the code I am using to get price to show under description of charge code... I tried to use group concat by that pulled everything into one row....
SELECT
q.orgId AS carrier_org_id,
originDoorCity AS origin,
source AS port_of_loading,
destination AS port_of_destination,
destinationDoorCity AS destination,
qc.containerType AS equipment_type,
-- group_concat(s.description) as 'combined description',
if (s.description in ('Base Rate'), c.CODE, null) as 'Frt CUR',
if (s.description in ('Base Rate'), price, null) as 'Ocean Rate',
if (s.description in ('Bunker Adjustment Factor','BAF','Bunker Surcharges','Bunker Adjustment Factor (BAF)','Bunker Adjustment Fee','Bunker Surcharge'), price, null) as 'Bunker',
if (s.description in ('Terminal Handling Service- Origin','Origin Terminal Handling Charge','Terminal Handling Charge - Origin','Terminal Handling Service'), c.CODE, null) as 'OTHC CUR',
if (s.description in ('Terminal Handling Service- Origin','Origin Terminal Handling Charge','Terminal Handling Charge - Origin','Terminal Handling Service'), price, null) as 'Origin Terminal Handling',
if (s.description in ('International Ship & Port Facility Security'), c.CODE, null) as 'SEC CUR',
if (s.description in ('International Ship & Port Facility Security'), price, null) as 'Port Security',
if (s.description in ('Documentation Fee- Origin','Origin Document Fee','Origin Documentation Fee','Documentation Fee','Orgin Documentation Fee'), c.CODE, null) as 'DocFee CUR',
if (s.description in ('Documentation Fee- Origin','Origin Document Fee','Origin Documentation Fee','Documentation Fee','Orgin Documentation Fee'), price, null) as 'Origin Doc Fee',
if (s.description in ('DG Surcharge','Hazardous Fee','Dangerous Goods','Hazardous Surcharge','Haz Surcharge','Hazardous Additional Charge (HAZ)'), c.CODE, null) as 'Haz CUR',
if (s.description in ('DG Surcharge','Hazardous Fee','Dangerous Goods','Hazardous Surcharge','Haz Surcharge','Hazardous Additional Charge (HAZ)'), price, null) as 'Haz Surcharge',
qc.commodityType AS commodity_type,
validityStartDate AS valid_from,
validityDate AS valid_until,
buyer.id AS buyer_org_id,
detentionDaysOrigin AS detention_days_origin,
detentionDaysDestination AS detention_days_destination,
buyerContractId AS buyer_contract_id,
routedVia,
buyer.NAME AS buyerName,
carrierCode,
rateType,
q.sourceRateCardId
FROM
QuoteSurcharge qs
JOIN Currency c ON qs.currencyId = c.id
JOIN Surcharge s ON qs.surchargeId = s.id
JOIN Quote q ON qs.quoteId = q.id
JOIN QuoteCriteria qc ON q.quoteCriteriaId = qc.id
JOIN Org buyer ON q.buyerOrgId = buyer.id
LEFT JOIN CommodityTypeResponse cr ON qc.commodityResponseId = cr.id
WHERE
validityDate >= now()
AND s.archived != TRUE
AND q.archived != TRUE
AND rateType = 'tendered'
AND buyer.NAME != 'Haven Markets Pte. Ltd. (For Customer Test)'
AND buyer.NAME LIKE CONCAT( '%',{{buyerName}}, '%' )
AND qc.orderId IS NULL
ORDER BY
buyerName,
carrier_org_id,
valid_until,
port_of_loading,
port_of_destination,
equipment_type,
description,
price;
So as per the pic with fake data, want to move these amounts up to top row so there is one row.
sorry attached picture