title: Bind Pivot table for Gridview
body: <p>I am developing a roster application (asp.net with VB + sql server) to let user input shift duty record, proposed screen is as follows:</p>
  
  <p>sample screen:
  <a href="http://i326.photobucket.com/albums/k421/joeyan829/asp/SMW-1-1.jpg" rel="nofollow">http://i326.photobucket.com/albums/k421/joeyan829/asp/SMW-1-1.jpg</a></p>
  
  <p>for the database design, each staff will have one record per day.</p>
  
  <p><strong>Column name:</strong></p>
  
  <p>staff_key, shift_date, start_time, end_time, shift_patten, SL, VL, ML, PH, APH, etc.</p>
  
  <p><strong>sample data:</strong></p>
  
  <p>123, 23-5-2011, 9:00, 17:00, A, N, N, N, N, N</p>
  
  <p>123, 24-5-2011, 12:00, 19:00, B, N, N, N, N, N</p>
  
  <p>123, 25-5-2011, 12:00, 19:00, B, N, N, N, N, N</p>
  
  <p>I know I need to use pivot table and then bind it to gridview,</p>
  
  <p><strong>example</strong></p>
  
  <p>(Peter, COII) 23/5 in column 1, (Peter, COII) 24/5 in column 2..... for row 1</p>
  
  <p><strong>sql server statement:</strong></p>
  
  <pre><code>select troster.staff_key, shift_date, start_time, end_time, 
  shift_type, SL, VL, ML, PH, APH 
  from
  troster 
  right join 
  hris_leave.dbo.tstaff 
  on 
  hris_leave.dbo.tstaff.staff_key=troster.staff_key
  where 
  troster.shift_date 
  in 
  ('5/23/2011', '5/24/2011', '5/25/2011', '5/26/2011', 
  '5/27/2011', '5/28/2011', '5/29/2011')
  </code></pre>
  
  <p>But I still have no idea how to amend/write the above SQL for it to become a pivot table and function to bind it to the gridview.</p>