Cacher is the code snippet organizer for pro developers

declare @contactid int
set @contactid = '21785';

--WORKS	Finds Drugs and returns Drug class

with t as (

Select DISTINCT a.conditions from
(select CASE 
	WHEN medication like 'caduet'
	THEN 'HTN+Chol E78.0,I10'
	when medication like '%actoplus%'
		OR medication like '%actos%'
		OR medication like '%amaryl%'
		OR medication like '%avandamet%'
		OR medication like '%avandia%'
		OR medication like '%bromocript%'
		OR medication like '%byett%'
		OR medication like '%duetact%'
		OR medication like '%farxi%'
		OR medication like '%flexpen%'
		OR medication like '%fortamet%'
		OR medication like '%glimepi%'
		OR medication like '%glipiz%'
		OR (medication like '%gluco%' AND medication NOT like '%gluconat%')
		OR medication like '%glumet%'
		OR medication like '%glybur%'
		OR medication like '%glyset%'
		OR medication like '%humulin%'
		OR medication like '%insulin%'
		OR medication like '%invoka%'
		OR medication like '%janu%'
		OR medication like '%jardiance%'
		OR medication like '%jentadueto%'
		OR medication like '%kombiglyze%'
		OR medication like '%lantus%'
		OR medication like '%levemir%'
		OR medication like '%malog%'
		OR medication like '%metformin%'
		OR medication like '%novo%'
		OR medication like '%olog%'
		OR medication like '%onglyza%'
		OR medication like '%pramlintide%'
		OR medication like '%Precose%'
		OR medication like '%ryzodeg%'
		OR medication like '%sitaglip%'
		OR medication like '%symlin%'
		OR medication like '%toujeo%'
		OR medication like '%tradj%'
		OR medication like '%tresiba%'
		OR medication like '%trulicity%'
		OR medication like '%victoz%'
		OR medication like '%welchol%'
	THEN 'Diabetes'
	WHEN medication like '%altocor%'
		OR medication like '%altoprev%'
		OR medication like '%caduet%'
		OR medication like '%colesevelam%'
		OR medication like '%crestor%'
		OR medication like '%ezetimibe%'
		OR medication like '%fenofib%'
		OR medication like '%gemfibrozil%'
		OR medication like '%lipitor%'
		OR medication like '%livalo%'
		OR medication like '%lomitapide%'
		OR medication like '%lopid%'
		OR medication like '%mevachor%'
		OR medication like '%niacin%'
		OR medication like '%niacor%'
		OR medication like '%niaspan%'
		OR medication like '%praluent%'
		OR medication like '%pravachol%'
		OR medication like '%repatha%'
		OR medication like '%tricor%'
		OR medication like '%trilipix%'
		OR medication like '%vastatin%'
		OR medication like '%vytorin%'
		OR medication like '%welchol%'
		OR medication like '%zetia%'
		OR medication like '%zocor%'
	THEN 'Cholesterol, E78.0'
	WHEN medication like '%-nitro%'
		OR medication like '%accupril%'
		OR medication like '%accuretic%'
		OR medication like '%aceon%'
		OR medication like '%adalat%'
		OR medication like '%afeditab%'
		OR medication like '%aldactone%'
		OR medication like '%aldoril%'
		OR medication like '%aliskiren%'
		OR medication like '%altace%'
		OR medication like '%amlobenz%'
		OR medication like '%amlodipine%'
		OR medication like '%anolol%'
		OR medication like '%april%'
		OR medication like '%aquazide%'
		OR medication like '%atacand%'
		OR medication like '%avalide%'
		OR medication like '%avapro%'
		OR medication like '%azor%'
		OR medication like '%benazepril%'
		OR medication like '%benicar%'
		OR medication like '%blocadren%'
		OR medication like '%butolol%'
		OR medication like '%bystolic%'
		OR medication like '%caduet%'
		OR medication like '%calan%'
		OR medication like '%capoten%'
		OR medication like '%capozide%'
		OR medication like '%captopril%'
		OR medication like '%cardizem%'
		OR medication like '%cardura%'
		OR medication like '%cartia%'
		OR medication like '%carvedilol%'
		OR medication like '%catapres%'
		OR medication like '%chlorthalidone%'
		OR medication like '%clonidine%'
		OR medication like '%coreg%'
		OR medication like '%corgard%'
		OR medication like '%corzide%'
		OR medication like '%covera%'
		OR medication like '%cozaar%'
		OR medication like '%dilt%'
		OR medication like '%diltiazem%'
		OR medication like '%diovan%'
		OR medication like '%diuril%'
		OR medication like '%doxazosin%'
		OR medication like '%dyazide%'
		OR medication like '%epril%'
		OR medication like '%esidrix%'
		OR medication like '%exforge%'
		OR medication like '%felodipine%'
		OR medication like '%furosemide%'
		OR medication like '%hctz%'
		OR medication like '%hydralazine%'
		OR medication like '%hydrochlorothiazide%'
		OR medication like '%hytrin%'
		OR medication like '%hyzaar%'
		OR medication like '%inderal%'
		OR medication like '%innopran%'
		OR medication like '%inspra%'
		OR medication like '%ipril%'
		OR medication like '%isoptin%'
		OR medication like '%labetalol%'
		OR medication like '%lasix%'
		OR medication like '%losartan%'
		OR medication like '%lotensin%'
		OR medication like '%lotrel%'
		OR medication like '%lozol%'
		OR medication like '%matzim%'
		OR medication like '%maxzide%'
		OR medication like '%methyldopa%'
		OR medication like '%metolazone%'
		OR medication like '%micardis%'
		OR medication like '%microzide%'
		OR medication like '%minipress%'
		OR medication like '%minitran%'
		OR medication like '%minoxidil%'
		OR medication like '%nebivolol%'
		OR medication like '%nexiclon%'
		OR medication like '%nifedical%'
		OR medication like '%nifedipine%'
		OR medication like '%nitrek%'
		OR medication like '%nitro-%'
		OR medication like '%nitrocot%'
		OR medication like '%nitrogard%'
		OR medication like '%nitrol%'
		OR medication like '%nolol%'
		OR medication like '%normodyne%'
		OR medication like '%norvasc%'
		OR medication like '%opril%'
		OR medication like '%opril%'
		OR medication like '%pressor%'
		OR medication like '%prinivil%'
		OR medication like '%prinzide%'
		OR medication like '%prolol%'
		OR medication like '%prolol%'
		OR medication like '%sartan%'
		OR medication like '%sectral%'
		OR medication like '%spironolactone%'
		OR medication like '%taztia%'
		OR medication like '%tekt%'
		OR medication like '%tenormin%'
		OR medication like '%terazosin%'
		OR medication like '%thiazide%'
		OR medication like '%tiazac%'
		OR medication like '%toprol%'
		OR medication like '%tribenzor%'
		OR medication like '%vasotec%'
		OR medication like '%verapamil%'
		OR medication like '%verelan%'
		OR medication like '%zebeta%'
		OR medication like '%zestoretic%'
		OR medication like '%zestril%'
		OR medication like '%ziac%'
		OR medication like '%zosin%'
	THEN 'Hypertension, I10'
	ELSE NULL
	END as Conditions
from prescriptions
where patientid = @contactid
and status = 'Active'
) 
a
where a.conditions is not null
--WORKS
)
select stuff((select CHAR(13)+CHAR(10) + conditions
              from t
              for xml path(''), TYPE
             ).value('.', 'NVARCHAR(MAX)'
                    ), 1, 1, '') as Conditions;