Converting Multiple Rows into HTML Format single ROW - Using T-SQL

Today I came across a scenario that required to convert multiple rows from a sql table in to single string i.e. string separated through HTML tags. Task seemed to be difficult and lengthy but actually it was not. Here are the steps through which I converted multiple rows into single HTML string.

Problem Statement

I have a table with values:

https://i1.gallery.technet.s-msft.com/converting-multiple-rows-6e007ace/image/file/134523/1/image_1.png

and I need output from given table in this fromat, so that I can get all the details of student Neu,Alice for same date in single row but in html format i.e. Header with bold and details in new line.  

https://i1.gallery.technet.s-msft.com/converting-multiple-rows-6e007ace/image/file/134524/1/results.png

Table Creation Script
Here is the table script used by me for this purpose:

USE [TestDataBase]
GO
-------------------- CREATES TABLE
CREATE TABLE  [dbo].[TestTable](
    [SrNo] [int] NULL,
    [FName] [nvarchar](max) NULL,
    [LName] [nvarchar](max) NULL,
    [R_Date] [date] NULL,
    [Header] [nvarchar](max) NULL,
    [Detail] [nvarchar](max) NULL
)
--------------------INSERTS VALUES IN TABLE
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (1, N'Alice', N'Neu', CAST(0x0F390B00 AS  Date), N'Books', N'practical books')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (1, N'Alice', N'Neu', CAST(0x0F390B00 AS  Date), N'Books', N'book1 and drawing books')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (1, N'Alice', N'Neu', CAST(0x0F390B00 AS  Date), N'Stationery', N'pencils and rubbers')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (2, N'Sara', N'Loren', CAST(0x0F390B00 AS  Date), N'Books', N'practical copies')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (2, N'Sara', N'Loren', CAST(0x0E390B00 AS  Date), N'Books', N'book1 and drawing books')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (2, N'Sara', N'Loren', CAST(0x0E390B00 AS  Date), N'Stationery', N'pencils and rubbers')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (1, N'Alice', N'Neu', CAST(0x0E390B00 AS  Date), N'Notes', N'None')
GO
INSERT [dbo].[TestTable] ([SrNo], [FName], [LName], [R_Date], [Header], [Detail]) VALUES  (2, N'Sara', N'Loren', CAST(0x0F390B00 AS  Date), N'Notes', N'Chemistry')
GO

          
Solution
a. Below script converts rows to html string for single ID and Date:
            

USE [TestDataBase]
GO
DECLARE @VAR varchar(max);
SET @VAR = ''
SELECT @VAR = @VAR+ '<b>'+ header+ '</b><br>' +
detail +'<br><br>'
FROM  [TestDataBase].[dbo].[TestTable]
where Srno ='1'  and [R_Date] ='2014-09-28'
SELECT   @VAR
GO

            
Our main purpose has been achieved after step 2. We’ve got the HTML format string for Srno=’1′ and single date value, to get HTML strings for all unique SrNo & Date values we need to follow below step as well.
            
b. Create a cursor i.e. a loop just like in procedural languages. and repeat step 2 as:

          

DECLARE @VAR varchar(max),
@ID int  ,
@ddate date;
  
DECLARE Cur Cursor FAST_FORWARD  --- Cursor starts here
FOR
  
with cte as (  ---- common table expression
select
ROW_NUMBEr () over
(partition by  Srno ,[R_Date] 
order by  Srno ,[R_Date] desc) cc,
Srno ,[R_Date]
from [TestDataBase].[dbo].[TestTable]
)
select max(R_Date),max(srno)  from  cte
group by  srno ,R_Date
  
  
OPEN Cur FETCH NEXT  FROM Cur INTO @ddate, @ID
WHILE @@FETCH_STATUS = 0 BEGIN
  
SET @VAR = ''
  
SELECT @VAR = @VAR+ '<b>'+ header+ '</b><br>' +  detail +'<br><br>'  --- converts rows into html string
FROM  [TestDataBase].[dbo].[TestTable]
where Srno =@ID and [R_Date] =@ddate
SELECT   @ID,@ddate,@VAR
  
FETCH NEXT  FROM Cur INTO @ddate,@Id   --- fetching next values of date & ID
END CLOSE  Cur
DEALLOCATE Cur
  
GO

c. If html string is required only for max date values, cte will be changed as below keeping remaining sql script same:

with cte as (
select
ROW_NUMBEr () over
(partition by  Srno ,[R_Date]  order by  Srno ,[R_Date] desc) cc,
Srno ,[R_Date]
  
from [TestDataBase].[dbo].[TestTable]
)
select max(R_Date),srno  from  cte
group by  srno ---- change occurs here
GO

 To verify the results, you may copy the generated string to a notepad and save it with .html extension. Open that file with your browser and you can view the results:

You can download the whole script from here