title: Using pivot table with column and row totals in sql server 2008
body: <p>I have a table with following columns </p>
  
  <pre><code>defect_id, developer_name, status, summary, root_cause, 
  Secondary_RC, description, Comments, environment_name
  </code></pre>
  
  <p>The column <code>root_cause</code> has Enviro, Requi, Dev, TSc,  TD, Unkn as its values and 
  column environment_name has QA1, QA2, QA3</p>
  
  <p>I need to prepare a report in the below format</p>
  
  <pre><code>    Enviro Requi  Dev TSc  TD Unkn  Total
  QA1    9    1     14   17   2   3   46
  QA2    8    1     14   0    5   1   29
  QA3    1    1      7   0    0   1   10
  Total 18    3     35   17   7   5   85
  </code></pre>
  
  <p>I have prepare the report till</p>
  
  <pre><code>    Enviro Requi  Dev  TSc  TD Unkn 
  QA1    9    1     14    17   2  3   
  QA2    8    1     14    0    5  1   
  QA3    1    1      7    0    0  1   
  </code></pre>
  
  <p>I used the below query to get the above result</p>
  
  <pre><code>select *
  from
  (
    select environment_name as " ", value
    from test1 
    unpivot
    (
       value
      for col in (root_cause)
    ) unp
  ) src
  pivot
  (
    count(value)
    for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
  ) piv
  </code></pre>
  
  <p>Can anyone help to get the totals for columns and rows?</p>