Create html table by using sql store procedure

          Hi ,
                                    In this article we will see how to create html table inside html table
by using sql server to show data of select statement.

step 1:: Create table

CREATE TABLE #TEMP (EMP_NAME VARCHAR(100),CHILD_NAME VARCHAR(100),CHILD_HOBBY VARCHAR(100),CHILD_AGE INT)

step 2:: Insert data in table

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','ADITYA','CRI',2)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','ADITYA','READ',2)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','PRANAV','CRI',4)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('KEDAR','PRANAV','READ',4)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','KHANds','TE',24)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','KHANsd','ME',42)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','IMRANd','JI',23)

insert into #temp (emp_name ,child_name,child_hobby,child_age) values('RAKESH','IMRANd','CI',23)

step 3:: Use below query for select data and create html table inside html table.

DECLARE @HTML_STR VARCHAR(MAX) SET @HTML_STR ='+<TABLE>+' declare @EMP_NAME VARCHAR(100) declare @getEmployee CURSOR set @getEmployee = CURSOR for select DISTINCT EMP_NAME from #TEMP open @getEmployee fetch next from @getEmployee into @EMP_NAME while @@FETCH_STATUS = 0 begin SET @HTML_STR=@HTML_STR+'<TR><TD>'+@EMP_NAME +'</TD>' -----------------INNER CUR 1 SET @HTML_STR=@HTML_STR+'<TD style="text-align :center" >' DECLARE @HTML_CHILD VARCHAR(MAX) SET @HTML_CHILD= '<TABLE style="width:100px;">' declare @CHILD_NAME VARCHAR(100) declare @getEmployee1 CURSOR set @getEmployee1 = CURSOR for select DISTINCT CHILD_NAME from #TEMP WHERE EMP_NAME=@EMP_NAME open @getEmployee1 fetch next from @getEmployee1 into @CHILD_NAME while @@FETCH_STATUS = 0 begin SET @HTML_CHILD=@HTML_CHILD+'<TR><TD >'+@CHILD_NAME +'</TD>' ----------------------------CUR 3 SET @HTML_CHILD=@HTML_CHILD+'<TD >' DECLARE @HTML_HO VARCHAR(MAX) SET @HTML_HO= '<TABLE >' declare @CHILD_HOBBY VARCHAR(100) declare @CHILD_AGE VARCHAR(100) declare @getEmployee2 CURSOR set @getEmployee2 = CURSOR for select CHILD_HOBBY,CHILD_AGE from #TEMP WHERE EMP_NAME=@EMP_NAME AND CHILD_NAME=@CHILD_NAME open @getEmployee2 fetch next from @getEmployee2 into @CHILD_HOBBY,@CHILD_AGE while @@FETCH_STATUS = 0 begin SET @HTML_HO=@HTML_HO+'<TR> <TD>'+@CHILD_NAME +'</TD><TD >'+@CHILD_AGE+'</TD></TR>' fetch next from @getEmployee2 into @CHILD_HOBBY,@CHILD_AGE end close @getEmployee2 deallocate @getEmployee2 SET @HTML_HO =@HTML_HO+'</TABLE>' SET @HTML_CHILD=@HTML_CHILD+@HTML_HO+'</TD></TR>' ---------------------------------CUR 3END fetch next from @getEmployee1 into @CHILD_NAME end close @getEmployee1 deallocate @getEmployee1 SET @HTML_CHILD =@HTML_CHILD+'</TABLE>' SET @HTML_STR=@HTML_STR+@HTML_CHILD+'</TD></TR>' --------------------------------INNER CUR 1 END fetch next from @getEmployee into @EMP_NAME end close @getEmployee deallocate @getEmployee SET @HTML_STR=@HTML_STR+'</TABLE>' PRINT @HTML_STR  

step 4::Output