sqlsql-serveradventureworks

How can I rotate a table that requires a group by without using pivot and case when in sql?


This is the code I'm trying to run

select min(sod.ModifiedDate) as [ModifiedDate]
    ,Bikes = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Bikes' and ModifiedDate = sod.ModifiedDatee)
    ,Components = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Components' and ModifiedDate = sod.ModifiedDate)
    ,Clothing = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Clothing' and ModifiedDate = sod.ModifiedDate )
    ,Accessories = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Accessories' and ModifiedDate = sod.ModifiedDate )

from SalesOrderDetail sod
inner join product p on p.ProductID = sod.ProductID
inner join ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
inner join ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID

group by ProductCategoryName
    ,sod.ModifiedDate
    ,datepart(year, sod.ModifiedDate) 
    ,datepart(month, sod.ModifiedDate) 
    ,datepart(day, sod.ModifiedDate)        

order by datepart(year, sod.ModifiedDate) 
    ,datepart(month, sod.ModifiedDate) 
    ,datepart(day, sod.ModifiedDate)

I can't figure out how to make it so it splits the LineTotal into the four ProductNameCategory like this: (expected result)

ModifiedDate Bikes Components Clothing Accessories
2005-07-01 00:00:00.000 467709.136900 31525.960400 2875.153600 1695.666000
2005-07-02 00:00:00.000 13931.520000 NULL NULL NULL
2005-07-03 00:00:00.000 15012.178200 NULL NULL NULL
2005-07-04 00:00:00.000 7156.540000 NULL NULL NULL
2005-07-05 00:00:00.000 15012.178200 NULL NULL NULL

All I get is this, it adds all the lineTotal for a given date regardless of ProductCategoryName and then puts the sum in Components, except when the only thing there is that day is Bikes, then he puts it in Bikes.

ModifiedDate Bikes Components Clothing Accessories
2005-07-01 00:00:00.000 NULL 503805.916900 NULL NULL
2005-07-02 00:00:00.000 13931.520000 NULL NULL NULL
2005-07-03 00:00:00.000 15012.178200 NULL NULL NULL
2005-07-04 00:00:00.000 7156.540000 NULL NULL NULL
2005-07-05 00:00:00.000 15012.178200 NULL NULL NULL

How can I make it look like the expected result without Pivot and case when? I need to get the results showed here using four different methods to then test performance and I already used pivot and case when. I'm trying to use this method I found https://learn.microsoft.com/en-us/troubleshoot/sql/database-design/rotate-table for this specific query


Solution

  • Here's one way to do it without PIVOT or CASE, but it's really ugly as a result, even after pulling some of the joins and aggregations out of the main query:

    WITH ProductMeta AS 
    (
      SELECT p.ProductID, CatName = pc.Name
      FROM Production.Product AS p
      INNER JOIN Production.ProductSubcategory ps 
        on ps.ProductSubcategoryID = p.ProductSubcategoryID
      INNER JOIN Production.ProductCategory pc 
        on pc.ProductCategoryID = ps.ProductCategoryID
      WHERE pc.Name IN (N'Bikes',N'Components',N'Clothing',N'Accessories')
    ), Agg AS
    (
      SELECT date = CONVERT(date, sod.ModifiedDate), 
        pm.CatName, 
        LineTotal = SUM(LineTotal)
      FROM Sales.SalesOrderDetail sod
      INNER JOIN ProductMeta AS pm ON sod.ProductID = pm.ProductID
      GROUP BY CONVERT(date, sod.ModifiedDate), pm.CatName
    )
    SELECT a.date
        ,Bikes       = (SELECT SUM(LineTotal) FROM Agg 
                        WHERE date = a.date AND CatName = 'Bikes')
        ,Components  = (SELECT SUM(LineTotal) FROM Agg 
                        WHERE date = a.date AND CatName = 'Components')
        ,Clothing    = (SELECT SUM(LineTotal) FROM Agg 
                        WHERE date = a.date AND CatName = 'Clothing')
        ,Accessories = (SELECT SUM(LineTotal) FROM Agg 
                        WHERE date = a.date AND CatName = 'Accessories')
    FROM Agg AS a
    GROUP BY a.date;