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

Question

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

Answer 1

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;

Answer 2

This is what I came up with just modifying your code. After downloading AdventureWorks, I validated this runs and the output matches your table above (minus the superfluous 00:00:00 times);

select DISTINCT CAST(sod.ModifiedDate as date) as [ModifiedDate]
    , Bikes = (
        select sum(LineTotal) 
        from Sales.SalesOrderDetail as sod2
            inner join production.product p on p.ProductID = sod2.ProductID
            inner join production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
            inner join production.ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID
        where pc.[Name] = 'Bikes' 
            and CAST(sod2.ModifiedDate as date) = CAST(sod.ModifiedDate as date)
      )
    , Components = (
        select sum(LineTotal) 
        from Sales.SalesOrderDetail as sod2
            inner join production.product p on p.ProductID = sod2.ProductID
            inner join production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
            inner join production.ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID
        where pc.[Name] = 'Components' 
            and CAST(sod2.ModifiedDate as date) = CAST(sod.ModifiedDate as date)
    )
    , Clothing = (
        select sum(LineTotal) 
        from Sales.SalesOrderDetail as sod2
            inner join production.product p on p.ProductID = sod2.ProductID
            inner join production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
            inner join production.ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID
        where pc.[Name] = 'Clothing' 
            and CAST(sod2.ModifiedDate as date) = CAST(sod.ModifiedDate as date)
    )
    , Accessories = (
        select sum(LineTotal) 
        from Sales.SalesOrderDetail  as sod2
            inner join production.product p on p.ProductID = sod2.ProductID
            inner join production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
            inner join production.ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID
        where pc.[Name] = 'Accessories' 
            and CAST(sod2.ModifiedDate as date) = CAST(sod.ModifiedDate as date)
    )

from Sales.SalesOrderDetail sod
order by CAST(sod.modifiedDate as date)

Sample results:

EDIT: This query completes in less than 0.359 seconds.

WITH prelim as (
    select sod2.LineTotal, sod2.ModifiedDate, pc.Name as ProductCategoryName 
    from Sales.SalesOrderDetail as sod2
        inner join production.product p on p.ProductID = sod2.ProductID
        inner join production.ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
        inner join production.ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID
)
select DISTINCT CAST(sod.ModifiedDate as date) as [ModifiedDate]
    , Bikes = (
        SELECT SUM(LineTotal)
        FROM prelim as p
        WHERE p.ModifiedDate = sod.ModifiedDate
            AND p.ProductCategoryName = 'Bikes'
      )
    , Components = (
        SELECT SUM(LineTotal)
        FROM prelim as p
        WHERE p.ModifiedDate = sod.ModifiedDate
            AND p.ProductCategoryName = 'Components' 
    )
    , Clothing = (
        SELECT SUM(LineTotal)
        FROM prelim as p
        WHERE p.ModifiedDate = sod.ModifiedDate
            AND p.ProductCategoryName = 'Clothing' 
    )
    , Accessories = (
        SELECT SUM(LineTotal)
        FROM prelim as p
        WHERE p.ModifiedDate = sod.ModifiedDate
            AND p.ProductCategoryName = 'Accessories' 
    )

from Sales.SalesOrderDetail sod
order by CAST(sod.modifiedDate as date)