How to add 2nd price column in SQL when info is normally stored on 2 separate rows-CodePudding

I currently have a very simple SQL script, which provides the sell price for a special customer of ours, who gets Priceline "J". I'd like to add a column of Retail pricing as well for them to reference, which is normally Priceline "R". My query currently looks like this:

SELECT RS_PCL.SKU,
    RS_PCL.SKU_DESC,
    RS_PCL.PACKAGE,
    RS_PCL.PRICE AS [Sell Price],
    RS_PCL.STD AS [Start Date],
    RS_PCL.END AS [End Date]
FROM RS_PCL
WHERE RS_PCL.PRICELINE = "J"

Would anyone be able to help me figure out how I could add the "R" price line as another column instead of separate rows? I can add WHERE RS_PCL.PRICELINE IN ("J","R") but it would create a separate row for each Retail price, instead of in the column next to it. I've seen examples of a separate SELECT CASE WHEN, but not sure exactly how the syntax works.

The prices are always going to have the same start date, so it would just need to join where the SKU matches and the Start Date matches.

NOTE : IM sorry I accidentally edited your answer, not my question...

Revised: Running into errors with this code still, saying "Your query does not include the specified expression 'GL Type' as part of an aggregate function" or would say each field that isn't included in the group by clause

SELECT RS_PCL.[GL Type],
 RS_PCL.SKU,
 RS_PCL.[SKU Desc],
 RS_PCL.Supplier,
 RS_PCL.[Case UPC],
 RS_PCL.[Pack UPC],
 RS_PCL.[Unit UPC],
 RS_PCL.PDCN,
 RS_PCL.Package,
 RS_PCL.[Price Start Date],
 RS_PCL.[Price End Date],
    Iif( RS_PCL.PRICELINE = "J" , RS_PCL.Price , Null) AS [Sell Price],
    Iif( RS_PCL.PRICELINE = "R" , RS_PCL.Price , Null) AS [Retail Price],
 RS_PCL.Cost,
 RS_PCL.Tax,
 RS_PCL.Freight
FROM RS_PCL
WHERE (RS_PCL.PRICELINE IN ("J","R"))

Tested the Self Join query listed below, I keep getting a "Syntax Error in FROM Clause" with this query.

SELECT J.*, R.R_PRICE FROM RS_PCL AS J
INNER JOIN SELECT (RS_PCL.SKU, RS_PCL.[Price Start Date], RS_PCL.Price AS R_PRICE FROM RS_PCL WHERE RS_PCL.PRICELINE = "R") AS R
ON J.SKU = R.SKU AND J.[Price Start Date] = R.[Price Start Date]
WHERE RS_PCL.Priceline = "J"

CodePudding user response：

SELECT CASE does not work in Access query. Use IIf() or Switch() or Choose().

Perhaps a CROSSTAB query or emulate CROSSTAB with expressions to create fields. Presuming, as indicated in question title, there is one "J" and one "R" record for each data combination:

SELECT SKU, SKU_DESC, PACKAGE, STD, END, 
    Max(IIf(PRICELINE = "J", PRICE, Null)) AS J, 
    Max(IIf(PRICELINE = "R", PRICE, Null)) AS R 
FROM RS_PCL
GROUP BY SKU, SKU_DESC, PACKAGE, STD, END

Or a self-join, assuming only fields needed as unique identifier are SKU and STD:

SELECT J.*, R.R_PRICE FROM RS_PCL AS J 
INNER JOIN (SELECT SKU, STD, PRICE AS R_PRICE FROM RS_PCL WHERE PRICELINE = "R") AS R 
ON J.SKU = R.SKU AND J.STD = R.STD
WHERE PRICELINE = "J";

This would be useful on a report but useless for a data entry form.

Yet another approach would use DLookup() domain aggregate function. Domain aggregate function can cause slow performance in query or form but the dataset would be editable.