How to write an SQL Server stored procedure for the following?

Question

I have a table like below:

create table testjob
(
   jobid int,  
   jobname varchar(100),  
   time float,  
   name varchar(50)  
)

insert into testjob values ( 1001,'java work',4.5,'arjun')  
insert into testjob values ( 1005,'sql work',10,'arjun')  
insert into testjob values ( 1010,'.net work',7.5,'arjun')  
insert into testjob values ( 1040,'java work',5.5,'ravi')  
insert into testjob values ( 1023,'php work',2.5,'arjun')  
insert into testjob values ( 1027,'.net work',3.5,'ravi')

I want a procedure so that my output as below in the html format like below:

Name: Arjun (24.5 Hrs spent)

jobname Time

java work 4.5  
sql work 10  
.net work 7.5  
php work 2.5    

Name: Ravi(9 Hrs spent)

jobname time

java work 5.5  
.net work 3.5

SQL Server is a data-storage and data-management system. You can write a stored procedure to return the **raw data** that will go into that report - but it's **not** SQL Server's job to format that as HTML - that's something your front-end (ASP.NET, PHP - whatever) is resposible for. — marc_s, Sep 27 '12 at 04:55
If it is possible you can try SSRS. But maybe this solution is too heavy for your task. — Mikhail Payson, Sep 27 '12 at 05:15
What language are you using in between your client and DB? It might be better to do this html generation at that layer in a more graceful way. — Vikdor, Sep 27 '12 at 05:23

score 1 · Answer 1 · edited May 23 '17 at 10:29

Inspired by this and this and this

--detail template
declare @strDTmp nvarchar(200) = '<td>[job]</td><td>[hrs]</td>' 
--sum template
declare @strSTmp nvarchar(200) = 'Name: [name] ([hrs] Hrs spent)' 
--Table head
declare @strThead nvarchar(200) = '<th>jobname</th><th>Time</th>'

  select 
    replace(replace(@strSTmp,'[name]',name),'[hrs]',stime) h1
    ,(
      select
        tr
      from
        (
        select
          0 pos
          ,name
          ,@strThead tr
        from
          testjob
        group by
          name
        union all
        select
          1 pos
          ,name
          ,replace(replace(@strDTmp,'[job]',jobname),'[hrs]',time) tr
        from
          testjob
        ) t
      where
        t.name = sumtable.name
      order by 
        pos
      FOR XML PATH(''), root('table'), type
      ) 
  from
    (
    select
      name
      ,sum(time) stime
    from
      testjob sumtable
    group by
      name
    ) sumtable
  FOR XML PATH ('')

Test on SQL Fiddle

Result:

<h1>Name: arjun (24.5 Hrs spent)</h1>
<table>
  <tr><th>jobname</th><th>Time</th></tr>
  <tr><td>java work</td><td>4.5</td></tr>
  <tr><td>sql work</td><td>10</td></tr>
  <tr><td>.net work</td><td>7.5</td></tr>
  <tr><td>php work</td><td>2.5</td></tr>
</table>
<h1>Name: ravi (9 Hrs spent)</h1>
<table>
  <tr><th>jobname</th><th>Time</th></tr>
  <tr><td>java work</td><td>5.5</td></tr>
  <tr><td>.net work</td><td>3.5</td></tr>
</table>

How to write an SQL Server stored procedure for the following?

1 Answers1