asked
Case Statement Assistance
I have a case statement that almost works...I have three conditions to be evaluated - there could be results that meet two or all three conditions so if that results is true then I need comma's to separate the results if none of them are true then I need simple 'N/A'.... I am close below is the code and partial results SELECT DISTINCT nm.EM_ITEM_NUM, nm.SELL_DSCR, nm.GNRC_ID, nm.GNRC_NAM, -- Family Name ISNULL( MAX(CASE WHEN ia.EM_ITEM_NUM IS NOT NULL THEN 'Inhalation Anesthetic' ELSE '' END)+','+ MAX(CASE WHEN npq.EM_ITEM_NUM IS NOT NULL THEN 'Nova + Equiv' ELSE '' END)+','+ MAX(CASE WHEN pnj.EM_ITEM_NUM IS NOT NULL THEN 'Injectable' ELSE '' END),'N/A') AS BLK_RSN, **************************************************** Results Inhalation Anesthetic,, ,Nova + Equiv,Injectable ,Nova + Equiv,Injectable Inhalation Anesthetic,, Inhalation Anesthetic,,Injectable Inhalation Anesthetic,,Injectable ,, do you see how some results start with commas...and there is no N/A
SELECT DISTINCT nm.EM_ITEM_NUM, nm.SELL_DSCR, nm.GNRC_ID, nm.GNRC_NAM, -- Family Name COALESCE(STUFF( MAX(CASE WHEN ia.EM_ITEM_NUM IS NOT NULL THEN ',Inhalation Anesthetic' ELSE '' END)+ MAX(CASE WHEN npq.EM_ITEM_NUM IS NOT NULL THEN ',Nova + Equiv' ELSE '' END)+ MAX(CASE WHEN pnj.EM_ITEM_NUM IS NOT NULL THEN ',Injectable' ELSE '' END),1,1,''),'N/A') The above will: produce a string like ',Inhalation anesthetic,Nove + Equiv, Injectible'. The STUFF function will remove the leading comma. If the string passed to STUFF is empty, STUFF will return NULL. COALESCE will return the first NON-NULL argument, so if STUFF doesn't return NULL, the comma-separated list will be returned, otherwise the second COALESCE-argument will be returned, which is 'N/A'.
That's why I removed the ** + ',' + ** and instead put the comma in the THEN-part of the CASE-statement. If none of the three values are included, there should be no comma either.
