sql server 2008 - SQL - Pivot table and group by not working

Question

I have a table as follows:

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

I need to use a pivot table on the [Type]/Count column and obtain a table as follows:

Product     #users  Date            Document Extraction
prodA       60      2013-03-02      113      152
prodB       45      2013-03-02      40       73

where the #user column is the sum group by product, and the Date is the max date group by product.

This is what I got so far:

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

but my final results give me something like this instead:

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

It is not grouping by the Product!

Any ideas?

EDIT:

So far, I have

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;

but my table1 above is actually made of codes below:

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 >= @StartDate AND ActDateTime <= @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;

so my question is, HOW do i put the chunk of code above that makes Table1 within the first WITH AS?

Thanks

Answer 1

You can't GROUP BY activity, document, extraction within the PIVOT table operator, the PIVOT operator infers the grouped columns automatically. But you can write it this way:

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;

SQL Fiddle Demo

This will give you:

| PRODUCT | TOTALUSERS |                   LATESTDATE | DOCUMENT | EXTRACTION |
-------------------------------------------------------------------------------
|   prodA |         60 | March, 02 2013 02:00:00+0000 |      113 |        152 |
|   prodB |         45 | March, 02 2013 02:00:00+0000 |       40 |         73 |

---

Update 1

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] 
  from activitylog
  where pageURL not like '%home%' 
    AND pageURL not like '/Default%'
  group by activity, 
           username, 
           ...
), Pivoted
AS
(
  SELECT *
  FROM a
  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;