Here is one way
SELECT
P_ID,
CASE
WHEN COUNT(DISTINCT LineOfBusiness) = 1 AND MAX(LineOfBusiness) = 'Commercial' THEN 'Commercial'
WHEN COUNT(DISTINCT LineOfBusiness) = 1 AND MAX(LineOfBusiness) = 'Non Commercial' THEN 'Non Commercial'
ELSE 'Commercial & Non Commerical'
END AS LineOfBusiness
FROM
YourTable
GROUP BY
P_ID;
If you don't expect the data to be used where the collations may be different, it perhaps could be simplified to something like shown below (but I would prefer my first query because it is unambiguous regardless of collation)
SELECT
P_ID,
CASE
WHEN MAX(LineOfBusiness) = 'Commercial' THEN 'Commercial'
WHEN Min(LineOfBusiness) = 'Non Commercial' THEN 'Non Commercial'
ELSE 'Commercial & Non Commerical'
END AS LineOfBusiness
FROM
YourTable
GROUP BY
P_ID;
No comments:
Post a Comment