title: Pivot One Column and Retain SUM,AVG,COUNT Columns
body: <p>Let's say I have a table PRODUCT like this.</p>
  
  <pre><code>ProductName    Amount
  ---------------------
  Soap           1.10
  Toothbrush     2.00
  Towel          3.00
  Soap           3.00
  
  </code></pre>
  
  <p>Then I get their sum, average and count using group by.</p>
  
  <pre><code>SELECT 
      [ProductName],
      SUM([Amount]) AS 'Sum', 
      COUNT(*) AS 'Count',
      AVG([Amount]) AS 'Avg'
  FROM Product
  GROUP BY [ProductIdName]
  </code></pre>
  
  <p>I have this result.</p>
  
  <pre><code>ProductName  Sum  Count  Avg
  -----------------------------
  Soap         4.10     2  2.05 
  Toothbrush   2.00     1  2.00 
  Towel        3.00     1  3.00
  </code></pre>
  
  <p>I want to pivot the productname column into something like this.</p>
  
  <pre><code>Type               Soap  Toothbrush  Towel
  ------------------------------------------
  Products Sum       4.10        2.00   3.00 
  Products Count        2           1      1
  Products Average   2.05        2.00   3.00
  </code></pre>
  
  <p>I have used pivot to each aggregate then union all, which I think is too verbose and not efficient? So I'm thinking if there are other solutions for this.</p>
  
  <pre><code>    SELECT 
          'Products Sum' AS [Type],
          *
      FROM Product t
      PIVOT(
          SUM(Amount)     
          FOR [ProductName] IN (
              [Soap], 
              [Toothbrush], 
              [Towel]
          )
      ) AS pivot_table
      UNION ALL
      SELECT 
          'Products Count' AS [Type],
          *
      FROM Product t
      PIVOT(
          COUNT(Amount)       
          FOR [ProductName] IN (
              [Soap], 
              [Toothbrush], 
              [Towel]
          )
      ) AS pivot_table
      ...etc ( pivot AVG(Amount) )
  </code></pre>