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

Mahmoud Gamal · Accepted Answer · 2013-03-03T07:45:37.347

19

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;

edited Mar 03 '13 at 07:45

answered Mar 03 '13 at 06:50

Mahmoud Gamal

78,257
17
139
164

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 Mar 03 '13 at 07:09
but it doesnt work...as I get a number of errors including: Incorrect syntax near the keyword 'WITH'. – viv_acious Mar 03 '13 at 07:11
@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? – Mahmoud Gamal Mar 03 '13 at 07:16
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? – viv_acious Mar 03 '13 at 07:17
@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**](http://www.sqlfiddle.com/#!3/b70440/8) – Mahmoud Gamal Mar 03 '13 at 07:19
Also if WITH is not the first statement that you are executing in the query batch then use ;WITH. Note the ";" before WITH. – Praveen Nambiar Mar 03 '13 at 07:24
Mahmoud: I've just edited my question to show you what I'm trying to achieve. Thanks! – viv_acious Mar 03 '13 at 07:27
@viv_acious - Note that, the semicolon before `WITH` it is just a sort of practice, see these for more information: http://www.sqlskills.com/blogs/bobb/yet-another-semicolon-rule-in-sql-server-denali/, https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons – Mahmoud Gamal Mar 03 '13 at 08:10

score 0 · Answer 2 · answered Mar 03 '13 at 07:08

You can achieve it using CTE.

;WITH CTE  
AS  
(  
    SELECT *   
    FROM Sample  
    PIVOT  
       (SUM([COUNT]) FOR [TYPE] IN (Document, Extraction)) AS PI  
)  

SELECT   
     Product,  
     SUM(Users) AS Users,  
     MAX(Date) AS Date,  
     MAX(Document) AS Document,  
     MAX(Extraction) AS Extraction  
FROM CTE  
GROUP BY Product;

SQL Fiddle Demo

SQL - Pivot table and group by not working

2 Answers2

SQL Fiddle Demo

Update 1

Linked