--===== If the test table already exists, drop it
     IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
        DROP TABLE #mytable

--===== Create the test table with 
 CREATE TABLE #mytable 
        (
        pms_id			INT, 
        eq_id			VARCHAR(20),
        bl_id			VARCHAR(8),
        fl_id			VARCHAR(2),
        rm_id			VARCHAR(8),
		interval_type	VARCHAR(4),
		instructions	VARCHAR(1000)
        )

--===== Insert the test data into the test table
 INSERT INTO #mytable 
       (pms_id, eq_id, bl_id, fl_id, rm_id, interval_type, instructions)
 SELECT 1, '346-BSC-022','346','01', NULL, 'mm', 'Do step 1' UNION ALL
 SELECT 2, '346-BSC-022','346','01', NULL, 'yy', 'Do step 2' UNION ALL
 SELECT 3, '346-BSC-021','346','01', NULL, 'wk', 'Do step 3' UNION ALL
 SELECT 4, '346-ASQ-01','346','01', '107', 'yy', 'Do something' UNION ALL
 SELECT 5, NULL,'346','01', '187', 'mm', 'Clean Area' UNION ALL
 SELECT 6, NULL,'346','01', '187', 'wk', 'Wipe sink Area' UNION ALL
 SELECT 7, NULL,'346','01', NULL, 'mm', 'Check Drains' UNION ALL
 SELECT 8, NULL,'346','01', NULL, 'wk', 'Inspect door' 

eq_id		bl_id	fl_id	rm_id	instructions
346-BSC-022	346	01	NULL	Do step 1 & Do step 2
346-BSC-021	346	01	NULL	Do step 3
346-ASQ-01	346	01	107	Do something
NULL		346	01	187	Clean Area & Wipe sink Area
NULL		346	01	NULL	Check Drains & Inspect door

WITH RN AS (SELECT ROW_NUMBER () OVER (ORDER BY pms_id) RN1, 
                   ROW_NUMBER() OVER (PARTITION BY eq_id,bl_id,fl_id,rm_id ORDER BY pms_id) RN2,
                   *
              FROM #mytable)

SELECT DISTINCT RN1 - RN2 OrderColumn, eq_id,bl_id,fl_id,rm_id, 
                REPLACE(STUFF((SELECT ', ' + instructions
                         FROM RN i
                        WHERE i.RN1 - RN2 = o.RN1 - o.RN2
                          FOR XML PATH('')),1,2,''),',',' &')
  FROM RN o
  ORDER BY RN1 - RN2

CREATE FUNCTION [dbo].[GetPmSteps] 
(
	-- Add the parameters for the function here
	@eq_id char(12),
	@bl_id char(8),
	@fl_id char(4),
	@rm_id char(8),
	@dateToDo datetime
)
RETURNS VARCHAR(MAX)
AS

BEGIN
	-- Declare the return variable here
	DECLARE @steps VARCHAR(MAX)
	
	SELECT  @steps =  COALESCE(@steps + CHAR(13) + CHAR(13), '') + '====== ' + UPPER(pm_procedure.description) + ' ======' + CHAR(13) + pm_procedure.instructions
	FROM         pm_schedule INNER JOIN
						  pm_procedure ON pm_schedule.pmp_id = pm_procedure.pmp_id
	WHERE     (pm_schedule.date_next_todo <= @dateToDo) AND (pm_schedule.active = 1) 
						  AND (@eq_id IS NULL OR pm_schedule.eq_id = @eq_id) AND (@bl_id IS NULL OR pm_schedule.bl_id = @bl_id) 
						  AND (@fl_id IS NULL OR pm_schedule.fl_id = @fl_id) AND (@rm_id IS NULL OR pm_schedule.rm_id = @rm_id)
	ORDER BY pm_schedule.interval_type, pm_schedule.interval_freq DESC

	-- Return the result of the function
	RETURN @steps
END

SELECT     eq_id, bl_id, fl_id, rm_id, dbo.GetPmSteps(eq_id, bl_id, fl_id, rm_id, getdate()) as steps
FROM         pm_schedule
WHERE     (active = 1) AND (date_next_todo <= getdate())
GROUP BY eq_id, bl_id, fl_id, rm_id