filter for a certain string in a certain column otherwise another string in another column

manufacturer_status

I would like Achieved target data for each organization. If it has no Achieved target data then I Partial target data. If both Achieved and Partial are not there then no data.

Expected Output

I tried:

Select * from manufacturer_status where target='Achieved'

This will give only organization ABC.

Select * from manufacturer_status where target in ('Achieved', 'Partial')

This will give ABC twice, for Achieved and Partial.

Looks like some conditional aggregation would do the trick

SELECT
  ms.Organization,
  ISNULL(
    MIN(CASE WHEN ms.Target = 'Achieved' THEN ms.Year END),
    MIN(CASE WHEN ms.Target = 'Partial'  THEN ms.Year END)
  ) AS Year,
  ISNULL(
    MIN(CASE WHEN ms.Target = 'Achieved' THEN ms.Target END),
    MIN(CASE WHEN ms.Target = 'Partial'  THEN ms.Target END)
  ) AS Target
FROM manufacturer_status ms
WHERE ms.Target IN ('Achieved', 'Partial')
GROUP BY
  ms.Organization;

You could also use window functions, relying on the fact that Achieved comes before Partial in the ordering.

SELECT
  ms.Organization,
  ms.Year,
  ms.Target
FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY ms.Organization ORDER BY ms.Target, ms.Year) AS rn
    FROM manufacturer_status ms
    WHERE ms.Target IN ('Achieved', 'Partial')
) ms
WHERE ms.rn = 1;

db<>fiddle