您不能
GROUP BY activity, document,extraction在
PIVOT表运算符内，该
PIVOT运算符会自动推断出分组的列。但是您可以这样写：
WITH PivotedAS( 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 ExtractionFROM PivotedGROUP BY Product;
SQL小提琴演示
这将为您提供：
| 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 aAS( 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, ...), PivotedAS( 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 ExtractionFROM PivotedGROUP BY Product;
