Server数据透视表和组

Question

我试图将我的一些行枢轴化，目前我的查询如下：

  SELECT count(distinct users.userName) as TotalUsers, products.productNameCommon, employees.Division, products.productType
FROM FlexLM_users users
INNER JOIN Org.Employees employees ON employees.Username=users.userName
INNER JOIN FlexLM_history history ON users.userID=history.userID
INNER JOIN FlexLM_products products ON products.productID=history.productID
where products.productType = 'Base'
GROUP BY  products.productNameCommon, employees.Division, products.productType
ORDER BY users DESC

并输出如下：

TotalUsers|   productNameCommon  |   Division            |  productType
------------------------------------------------------------------------
16        |  Standard            |  Disease Control      |  base
12        |  Basic               |  Epidemiology         |  base
10        |  Standard            |  Prevention           |  base
8         |  Advanced            |  Epidemiology         |  base
6         |  Basic               |  Disease Control      |  base
2         |  Advanced            |  Prevention           |  base

我想做的是：

Division       | Basic | Standard | Advanced | TotalUsers
----------------------------------------------------------
Disease Control|   6   |    16    |    0     |  22
Epidemiology   |   12  |    0     |    8     |  20
Prevention     |   0   |   10     |    2     |  12   

Answer 1

SELECT Division
     , ISNULL([Basic]   , 0)    AS [Basic]
     , ISNULL([Standard], 0)    AS [Standard]
     , ISNULL([Advanced], 0)    AS [Advanced]
     ,  ISNULL([Basic]   , 0)
      + ISNULL([Standard], 0) 
      + ISNULL([Advanced], 0)   AS TotalUsers

FROM (
        SELECT TotalUsers , productNameCommon , Division
        FROM  ( 
                        -- Your Existing Query here
              )a    
    ) t
PIVOT (
        SUM (TotalUsers)
        FOR productNameCommon
        IN ([Basic], [Standard] , [Advanced])
      ) p