title: how to get header and detail row from a result set?
body: <p>I have these three tables .</p>
  
  <pre><code>CREATE TABLE Employees (
      EmpID INT IDENTITY, 
      EmpName VARCHAR(100)
  )  
  GO    
  INSERT INTO Employees(EmpName)  
  SELECT 'John Torres' UNION ALL
  SELECT 'Irina Williams'
  
  SELECT * FROM Employees
  GO
  
  CREATE TABLE PayrollWeek(
      WeekID INT IDENTITY, 
      EmpID INT,
      WeekStart DATETIME,
      WeekEnd DATETIME
  )  
  GO    
  INSERT INTO PayrollWeek(EmpID,WeekStart,WeekEnd)  
  SELECT 1,'11-20-2011','11-26-2011' UNION ALL
  SELECT 2,'11-27-2011','12-03-2011' UNION ALL
  SELECT 1,'11-27-2011','12-03-2011'
  
  SELECT * FROM PayrollWeek
  GO
  
  CREATE TABLE EmployeeVisits(
      ID INT, 
      EmpID INT,
      VisitDate DATETIME,
      StartTime VARCHAR(5),
      EndTime VARCHAR(5),
      EarningCode VARCHAR(100)
  )  
  GO    
  INSERT INTO EmployeeVisits(ID,EmpID,VisitDate,StartTime,EndTime,EarningCode)  
  SELECT 1,1,'11-20-2011','10:00','12:00','Sat-Sun1' UNION ALL
  SELECT 2,1,'11-21-2011','13:30','16:00','Mon-Fri1' UNION ALL
  SELECT 3,1,'11-22-2011','14:00','15:00','Mon-Fri1' UNION ALL
  SELECT 4,1,'11-24-2011','10:00','14:00','Mon-Fri1' UNION ALL
  SELECT 5,1,'11-25-2011','13:30','16:00','Mon-Fri1' UNION ALL
  SELECT 6,1,'11-26-2011','14:00','15:00','Sat-Sun1' UNION ALL
  SELECT 7,2,'11-27-2011','09:00','11:00','Sat-Sun1' UNION ALL
  SELECT 8,2,'11-28-2011','07:00','12:00','Mon-Fri1' UNION ALL
  SELECT 9,2,'11-29-2011','09:00','11:00','Mon-Fri1' UNION ALL
  SELECT 10,2,'12-03-2011','07:00','12:00','Sat-Sun1'
  </code></pre>
  
  <p>Expected Result is this</p>
  
  <pre><code> RecordType EmpId   EmpName WeekStart   Weekend EarningCode Hour
      H            1     John Torres  11/20/2011  11/26/2011      
      D                                                  Sat-Sun1 3
      D                                                  Mon-Fri1 10
      H            2   Irina Williams 11/27/2011  12/3/2011       
      D                                                  Sat-Sun1 7
      D                                                  Mon-Fri1 7
  </code></pre>