SET NOCOUNT ON

INSERT INTO TBL_PATIENT_ALLERGIES_SUMMARY (PATIENT_CDR_ID, username)
	SELECT PATIENT_CDR_ID, username FROM TBL_PATIENT_ALLERGIES
	GROUP BY PATIENT_CDR_ID, username

DECLARE @allergies varchar(7000)
DECLARE @iRow int
DECLARE @iMax int
SELECT @iMax = count(*) from TBL_PATIENT_ALLERGIES_SUMMARY
SELECT @iRow = 1
While @iRow < @iMax

BEGIN

--build the csv string
SELECT @allergies = null
Select @allergies = Coalesce(@allergies + ', ','')+ CONVERT(varchar(30), ALLERGY_DATE,1) + '-' + ALLERGY_NAME +
	'RXN' = CASE
		WHEN REACTION IS NOT NULL THEN ' (' + REACTION + ')'
		WHEN REACTION IS NULL THEN ''
	END

FROM TBL_PATIENT_ALLERGIES
WHERE 
PATIENT_CDR_ID = (SELECT PATIENT_CDR_ID FROM TBL_PATIENT_ALLERGIES_SUMMARY WHERE sys_id = @iRow)


--UPDATE THE TABLE
UPDATE TBL_PATIENT_ALLERGIES_SUMMARY
	SET allergy_string = @allergies
	WHERE sys_id = @iRow

SELECT @iRow = @iRow + 1
END


SELECT * FROM TBL_PATIENT_ALLERGIES_SUMMARY

quote:

---

Take out 'RXN' = and you sold be ready to fly.

FYI Makes more sense to me Propopulate the Variable with empty string than to use the Coalesce function.

---