title: Using ROLLUP and GROUPING SETS to format results
body: <p>I'm trying to learn the <code>ROLLUP</code> and <code>GROUPING SETS</code> operators in SQL Server 2008 R2. My table looks like this:</p>
  
  <pre>
  +-------+---------+------------+------+-------+----------+------+
  | EmpID | EmpName | StartDate  | Type | Units | Account  | Dist |
  +-------+---------+------------+------+-------+----------+------+
  | 11111 | Alice   | 2015-09-04 | SD20 | 0.500 | 560.2200 |  100 |
  | 11111 | Alice   | 2015-09-08 | CCS  | 1.330 | 9P0.6010 |   38 |
  | 11111 | Alice   | 2015-09-08 | CCS  | 2.170 | 010.9055 |   62 |
  | 11111 | Alice   | 2015-09-10 | SD20 | 0.500 | LP0.3002 |  100 |
  | 11111 | Alice   | 2015-09-14 | SD20 | 0.500 | LP0.3002 |  100 |
  | 11111 | Alice   | 2015-09-17 | SD20 | 1.000 | LP0.P303 |  100 |
  | 11111 | Alice   | 2015-09-18 | SD20 | 1.000 | 600.2200 |  100 |
  | 11111 | Alice   | 2015-09-24 | SD20 | 1.000 | LP0.3002 |  100 |
  | 11111 | Alice   | 2015-09-25 | SD20 | 1.000 | LP0.3002 |  100 |
  | 11111 | Alice   | 2015-09-29 | SD20 | 1.000 | LP0.3002 |  100 |
  | 33333 | Carol   | 2015-09-03 | SD20 | 1.000 | 9P0.PP10 |  100 |
  | 33333 | Carol   | 2015-09-04 | SD20 | 0.200 | 600.6P62 |   20 |
  | 33333 | Carol   | 2015-09-04 | SD20 | 0.800 | 600.6P62 |   80 |
  | 33333 | Carol   | 2015-09-25 | SD20 | 1.000 | P50.2100 |  100 |
  +-------+---------+------------+------+-------+----------+------+
  </pre>
  
  <p>I want EmpID and EmpName to display once per employee, the Units to be subtotaled by Type, and to be ordered by EmpID, Type, Account, like so:</p>
  
  <pre>
  +-------+---------+----------------+-------+----------+
  | EmpID | EmpName |      Type      | Units | Account  |
  +-------+---------+----------------+-------+----------+
  | 11111 | Alice   |                |       |          |
  |       |         | CCS            | 2.17  | 010.9055 |
  |       |         | CCS            | 1.33  | 9P0.6010 |
  |       |         |       Subtotal | 3.5   |          |
  |       |         | SD20           | 0.5   | 560.2200 |
  |       |         | SD20           | 1     | 600.2200 |
  |       |         | SD20           | 4     | LP0.3002 |
  |       |         | SD20           | 1     | LP0.P303 |
  |       |         |       Subtotal | 6.5   |          |
  | 33333 | Carol   |                |       |          |
  |       |         | SD20           | 1     | 600.6P62 |
  |       |         | SD20           | 1     | 9P0.PP10 |
  |       |         | SD20           | 1     | P50.2100 |
  |       |         |       Subtotal | 3     |          |
  +-------+---------+----------------+-------+----------+
  </pre>
  
  <p>Here's my query</p>
  
  <pre><code>select 
  case
      when Account is null and Type is null then cast(EmpID as varchar(10))
      else ''
  end SubEIN, 
  case
      when Account is null and Type is null then EmpName 
      else ''
  end SubName, 
  case
      when Account is null and Type is not null then 'Subtotal:'
      else isnull(Type, '')
  end Type, 
  case
      when Account is null and Type is null then ''
      else cast(Units as varchar(10)) 
  end Units, 
  isnull(Account, '')
  from (
      select EmpID, EmpName, Type, sum(Units) AS Units, Account
      from mytable
      group by grouping sets ((EmpID, EmpName), (EmpID, EmpName, Type), (EmpID, EmpName, Account, Type))
  ) x
  order by x.EmpName, x.Type, x.Units, x.Account
  </code></pre>
  
  <p>Can I use the <code>ROLLUP</code> and/or <code>GROUPING SETS</code> operators to get what I want instead of all the <code>CASE</code> expressions?</p>