SELECT OPD_ID,
MAX(CASE WHEN CATEGORY_TYPE='FC' THEN PRICE ELSE NULL END) AS COST_PRICE,
MAX(CASE WHEN CATEGORY_TYPE='FS' THEN PRICE ELSE NULL END) AS SELL_PRICE
FROM tablename
GROUP BY OPD_ID

quote:

---

Originally posted by jeffw8713

In the query I gave you - replace 'table' with the name of your actual table. I have used the alias 'a' for the first table and 'b' for the second instance of the same table. To make it clearer - if your table name is 'ThisIsMyTable' then the query would be:

SELECT a.OPD_ID, a.Price AS [Cost Price], b.Price AS [Sell Price]
FROM ThisIsMyTable a
INNER JOIN ThisIsMyTable b ON b.OPD_ID = a.OPD_ID AND b.CATEGORY_TYPE = 'FS'
WHERE a.CATEGORY_TYPE = 'FC';

You could also use the AS clause to alias (I usually don't include it). That would be:

SELECT a.OPD_ID, a.Price AS [Cost Price], b.Price AS [Sell Price]
FROM ThisIsMyTable AS a
INNER JOIN ThisIsMyTable AS b ON b.OPD_ID = a.OPD_ID AND b.CATEGORY_TYPE = 'FS'
WHERE a.CATEGORY_TYPE = 'FC';

---

quote:

---

Originally posted by jeffw8713

visakh16

A row with a category_type of 'FS' defines a sell price, a row with a category_type of 'FC' defines a cost price. You can either join to the table for each type, or build a group by cross tab and a SUM(CASE ...) expressions. I prefer joining since it makes more sense to me - but, others will prefer using a cross tab. If there is a requirement to actually SUM the values and you have multiple rows with a category_type = 'FC' or 'FS' then the cross tab would be better.

---