This worked for me:

Declare @Reccom table (KeyValue varchar(20), USerTitle varchar(25), Date varchar(14), Details varchar(max))

Declare @temp table(row_id int,KeyValue varchar(20), USerTitle varchar(25), Date varchar(14), Details varchar(max))

Declare @count int

Declare @data varchar(max)

Set @count = 1

WHile @Count <= 10

Begin

INSERT INTO @Reccom (

KeyValue,

UserTitle,

Date,

Details

) VALUES

(

'Doctor Note',

'Mr Bob Saggett',

'23/10/1986',

CAST('the brown fox jumped over the silky terrier' AS VARCHAR(MAX))

)

Set @count = @count + 1

End

Set @count = 1

Set @data = ''

Insert into @temp

Select

row_number() Over(Order By Date) as row_id,

*

From

@Reccom

While @count <= (Select Max(row_id) From @temp)

Begin

Select

@data = @data + keyvalue + ' Note - ' + usertitle + ', ' +

date + char(13) + details + char(13)

From

@temp

Set @count = @count + 1

End

Select Len(@data), @data