Grouping / Reporting Question

Hi,

I've written a series of procedures that are writing output lines into a common table that I'm then reporting on.

I've included some simplified code to show waht I've done.

I was hoping someone could tell me if it is possible to remove the loop that I've created to get the report headings.

I've tried to write this as a with statement, but can't quite get it right.


--------


CREATE TABLE JobReport(
JobRunId int NOT NULL,
ReportDate datetime NOT NULL,
Process] varchar(200) NOT NULL,
Line varchar(200) NOT NULL
)


INSERT INTO jobreport(jobrunid,reportdate,process,line)
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A1' union
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A2' union
SELECT 1,CURRENT_TIMESTAMP,'Proc A','testline A3' union
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b1' union
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc b','testline b3' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c1' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c3' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc c','testline c4' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d1' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d2' UNION
SELECT 1,CURRENT_TIMESTAMP,'Proc d','testline d3'


SELECT IDENTITY(INT,1,1) id,headings.Process INTO #headings from
(
SELECT Process FROM jobreport
WHERE jobrunid = 1
GROUP BY Process
) headings


BEGIN
DECLARE @max INT, @COUNT INT,@heading AS VARCHAR(200),@report VARCHAR(MAX), @jobrunid INT
SET @jobrunid = 1
set @report = ''

SELECT @max = COUNT(*) FROM #headings
SET @count = 0

WHILE @count < @max
BEGIN
SET @count = @count + 1

SELECT @heading = h.Process FROM #headings h WHERE h.id = @count

SELECT @report = @report + '----' + @heading + '----' + CHAR(13)
SELECT @report = @report + line + CHAR(13)
FROM jobcontrol.dbo.jobreport rep
WHERE rep.jobrunid = @jobrunid
AND rep.Process = @heading
END

--------

any help would be appreciated.

thanks



Sean