Where you have multiple rows that you need to either display or report on as one single, concatenated field you can use the Coalesce function to merge them.

You can either do this in a stored procedure or create a function so it can be used inline in Select statements.

Below is an example of a function that will merge multiple policy numbers into one string containing them separated by commas.

CREATE FUNCTION dbo.fn_MergePolicyNums
(@HGNum as int)
RETURNS varchar(100) AS

BEGIN

declare @PolNums varchar(100) /* Holds multiple MEC numbers */

SELECT @PolNums = COALESCE (@PolNums + ', ' + PolicyNum, PolicyNum, @PolNums) FROM tblPolicy WHERE HGNum = @HGNum

RETURN ISNULL(@PolNums, '')
END