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