title: SQL - Pivot table and group by not working
body: <p>I have a table as follows:</p>
  
  <pre><code>Product     #users  Date            Count   Type
  prodA       29      2013-02-27      113     Document
  prodA       31      2013-03-02      152     Extraction
  prodB       17      2013-02-26      40      Document
  prodB       28      2013-03-02      73      Extraction
  </code></pre>
  
  <p>I need to use a pivot table on the [Type]/Count column and obtain a table as follows:</p>
  
  <pre><code>Product     #users  Date            Document Extraction
  prodA       60      2013-03-02      113      152
  prodB       45      2013-03-02      40       73
  </code></pre>
  
  <p>where the #user column is the sum group by product, and the Date is the max date group by product.</p>
  
  <p>This is what I got so far:</p>
  
  <pre><code>SELECT Product, 
         sum(users),
         max([Date]),  
         [Document],[Extraction] FROM Table
  
       PIVOT 
          ( sum([Count]) FOR [Type] IN ( Document , Extraction)) AS [QUANTITY]
  
       GROUP BY activity, document, extraction
  </code></pre>
  
  <p>but my final results give me something like this instead:</p>
  
  <pre><code>Product     #users Date      Document Extraction
  prodA       31     2013-03-02 NULL     152
  prodA       29     2013-02-27 113      NULL
  prodB       28     2013-03-02 NULL     73
  prodB       17     2013-02-26 40       NULL
  </code></pre>
  
  <p>It is not grouping by the Product!</p>
  
  <p>Any ideas?</p>
  
  <p>EDIT:</p>
  
  <p>So far, I have</p>
  
  <pre><code>WITH Pivoted
  AS
  (
    SELECT *
    FROM table1
    PIVOT 
    ( 
      sum([Count]) FOR [Type] IN ( Document , 
                                  Extraction)
    ) AS p
  ) 
  SELECT 
    product,
    SUM(Users) AS TotalUsers,
    MAX(DAte) AS LatestDate,
    MAX(Document) AS Document,
    MAX(Extraction) AS Extraction
  FROM Pivoted
  GROUP BY Product;
  </code></pre>
  
  <p>but my table1 above is actually made of codes below:</p>
  
  <pre><code>WITH a
  AS(
  SELECT    activity, 
                  username, 
                  [Last Accessed] = max(DATEADD(dd, DATEDIFF(d, 0, ActDateTime), 0)), --[#Users] = count(distinct username), 
                  CASE WHEN COUNT(activity)IS NOT NULL THEN 1 ELSE 0 END AS Count,
                  CASE WHEN pageURL LIKE '/Document%'
                  OR pageURL LIKE '/Database%' THEN 'Document' ELSE 'Extraction' END AS [Type] --into #temp
                  from activitylog
                  where pageURL not like '%home%' AND pageURL not like '/Default%'
                  --AND ActDateTime &gt;= @StartDate AND ActDateTime &lt;= @EndDate
                  group by activity, 
                  username, 
                  --department,
                  DATEADD(dd, DATEDIFF(d, 0, ActDateTime), 0), 
                  CASE WHEN pageURL LIKE '/Document%' OR pageURL LIKE '/Database%' THEN 'Document' ELSE 'Extraction' END 
                  --order by activity--, username, department,DATEADD(dd, DATEDIFF(d, 0, ActDateTime), 0)
      )
  
      ,b as
      (select activity, count(distinct username) as [Users] ,
          --department ,
          max([Last Accessed]) as [Last Accessed1],count([count])as [Count],[Type] from a --into #temp1 from #temp
          group by activity, 
          --department,
          [Type]
  
          )  
  
          select * from b order by activity;
  </code></pre>
  
  <p>so my question is, HOW do i put the chunk of code above that makes Table1 within the first WITH AS?</p>
  
  <p>Thanks</p>