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

Solution

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; 

Comments

[viv_acious]

I have a question - my Table provided is actually a result of the WITH name AS SELECT statement. So combining your code means I will have 2 WITH AS statements within another. I cant seem to get it to work. So i have something like WITH Pivoted AS (SELECT * FROM (WITH Pivoted AS a...this is how I get my Table)... Basically I used your code, but in place of table1, I have another (WITH AS statement).

[viv_acious]

but it doesnt work...as I get a number of errors including: Incorrect syntax near the keyword 'WITH'.

[Mahmoud Gamal]

@viv_acious, Yes, you can use mutiple CTEs with the same query like WITH CTE AS(...), CTE2 AS (...) SELECT ..., can you please edit your question and show me that query you tried and give you that error?

[viv_acious]

Sure! Actually I worked around this by creating a #temptable from my first WITH AS. Then used another WITH AS using that. Is there another different between this method and having multiple CTEs as you mentioned?

[Mahmoud Gamal]

@viv_acious - No, but there is no need for temp tables, as I said you can use multiple CTE's normally. Something like in this demo

[Praveen Nambiar]

Also if WITH is not the first statement that you are executing in the query batch then use ;WITH. Note the ";" before WITH.

[viv_acious]

Mahmoud: I've just edited my question to show you what I'm trying to achieve. Thanks!

[Mahmoud Gamal]

@viv_acious - Note that, the semicolon before WITH it is just a sort of practice, see these for more information: sqlskills.com/blogs/bobb/…, sqlblog.com/blogs/aaron_bertrand/archive/2012/09/25/16567.as‌​px