SQL-数据透视表和分组不起作用 [英] SQL - Pivot table and group by not working

问题描述

我有一张桌子，如下:

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

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

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

它不是按产品分组的！

有什么想法吗?

到目前为止，我有

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;

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

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;

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

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

谢谢

推荐答案

您不能在PIVOT表运算符中使用GROUP BY activity, document, extraction，PIVOT运算符会自动推断出分组的列.但是您可以这样写:

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小提琴演示

这将为您提供:

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 |

---

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

这篇关于SQL-数据透视表和分组不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！