Using PIVOT and JOIN together

Consider This Query:

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

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

  • SQL Server date format function
  • datediff with conditions in select statment
  • Subtracting Two Case Statements
  • Updating dimension tables using SQL Server (BIDs or Data Tools)
  • Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF when not trying to set a value for an IDENTITY column
  • SQL Server CE or Access for a portable database with c#?
  • enter image description here

    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             ...
    ...
    

    How I can do this?

    Thanks

  • Best way to migrate data from Access to SQL Server
  • SQL Server: limit SELECT to only fetch data from last 4 weeks / 1 month
  • SQL server query to find values grouped by one column but different in at least one of other columns
  • left join table on string like '%table.name%'
  • Bulk insert not recognizing row terminators
  • SQL Check constraint on column referencing other columns
  • One Solution collect form web for “Using PIVOT and JOIN together”

    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  
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.