一起使用 PIVOT 和 JOIN

2022-01-22 00:00:00 pivot sql tsql sql-server-2008 sql-server

考虑这个查询:

SELECT [Order Details].OrderID,
   c.CategoryName,
   COUNT(c.CategoryID)
FROM   [Order Details]
   INNER JOIN Products p
        ON  p.ProductID = [Order Details].ProductID
   INNER JOIN Categories c
        ON  c.CategoryID = p.CategoryID
GROUP BY
   [Order Details].OrderID,
   c.CategoryName
ORDER BY
   [Order Details].OrderID

这个查询返回这样的结果(Usnig Northwind 数据库):

this query returns this such result (Usnig Northwind Database):

我想使用 Pivot 和 Join 来获得这样的结果:

I want to use Pivot with Join to get such this result:

OrderID    Condiments    Produce    Seafood    Condiments    Grains/Cereals    ...
--------------------------------------------------------------------------------------
10250           1            1           1          0              0             ...
10251           1            0           0          0              2             ...
...

我该怎么做?

谢谢

推荐答案

WITH T
     AS (SELECT [Order Details].OrderID,
                c.CategoryName,
                c.CategoryID
         FROM   [Order Details]
                INNER JOIN Products p
                  ON p.ProductID = [Order Details].ProductID
                INNER JOIN Categories c
                  ON c.CategoryID = p.CategoryID)
SELECT *
FROM   T PIVOT ( COUNT (CategoryID) FOR CategoryName IN ( 
       [Beverages],
       [Condiments],
       [Confections], 
       [Dairy Products], 
       [Grains/Cereals],
       [Meat/Poultry],
       [Produce],
       [Seafood]) ) AS pvt
ORDER  BY OrderID

相关文章