SQL - 数据透视表和分组不工作

SQL - Pivot table and group by not working

提问by viv_acious

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:

我需要在 [Type]/Count 列上使用数据透视表并获取如下表：

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.

其中#user 列是按产品分组的总和，而日期是按产品分组的最大日期。

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:

但我上面的 table1 实际上是由下面的代码组成的：

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?

所以我的问题是，我如何将上面的代码块放在第一个 WITH AS 中？

Thanks

谢谢

回答by Mahmoud Gamal

You can't GROUP BY activity, document, extractionwithin the PIVOTtable operator, the PIVOToperator infers the grouped columns automatically. But you can write it this way:

您不能GROUP BY activity, document, extraction在PIVOTtable 运算符中，该PIVOT运算符会自动推断分组列。但是你可以这样写：

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

SQL 小提琴演示

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

更新 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; 

回答by Praveen Nambiar

You can achieve it using CTE.

您可以使用 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 小提琴演示