Consolidating rows
i'm trying to consolidate my data into one row, just having a hard time grouping this information.
this image is what i'm currently getting. I'm trying to get this into one row.
SELECT DISTINCT ccc.StartDate,ccc.StartTime,ccc.Enddate,ccc.EndTime,ccc.baseId,ccc.PegaIata,
CASE WHEN ccc.RowNumber = 1 THEN ccc.concessiontype ELSE NULL END AS ConcessionFeeAType,
CASE WHEN ccc.rowNumber = 1 THEN (ccc.taxrate) ELSE NULL END as ConcessionFeeA,
CASE WHEN ccc.RowNumber = 2 THEN ccc.concessiontype ELSE NULL END AS ConcessionFeeBType,
CASE WHEN ccc.rowNumber = 2 THEN (ccc.taxrate) ELSE NULL END as ConcessionFeeb
FROM
(
SELECT DENSE_RANK() OVER(PARTITION BY a.baseid ORDER BY a.taxRate) AS RowNumber, '12/31/2017' AS StartDate, '11:59' AS StartTime, '' AS Enddate, '' AS EndTime, a.baseid,b.PegaIata,
a.concessiontype,a.taxRate
FROM concessions AS a
JOIN [Bases] AS b ON b.baseid = a.baseId
JOIN dbo.ProductCodes AS c ON c.productCode = a.productCode
LEFT OUTER JOIN [Bridge] AS d ON d.productCode = a.productCode
WHERE c.fuelAssoc IS NULL AND a.taxRate <> 0) AS cccthis image is what i'm currently getting. I'm trying to get this into one row.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
As you have 2 rows, it will process 2 times and you can't do nothing unless you chance the SELECT in the FROM clause to return a single row.