How do I pivot a query in SQL Server-CodePudding

Select 
    Datename(Month,[date]) as Month, [Name], 
    count(Name) Total, 
    '$'   Cast(Sum(Total_Tax_Exclusive_Price) as varchar(15)) Gross_Revenue
From 
    dbo.Receipts
Where 
    [name] Like '%coffee Cake%'
    and [date] between '2022-09-15' and '2022-12-20'
Group By 
    Date, Total_Tax_Exclusive_Price, Name
Order by 
    month Desc, Total desc

The above code displays what I want however, I'd like to pivot that and don't know how to do that. I can do a simple pivot but my formulas aren't there.

I want to sum the counts and and dollar values for the months in pivot. My initial query may not be set up right to do this. I have included a picture of how it looks.

Working Query Image

I've tried the pivot function but it's not returning any results

I want it to look like this example Instead of vertical I want it to look like this example with coffee cake in left column

           | September | October | November | December
           |    35     |     25  |    18    |    36
           |   112.56  |  110.54 |   100.34 |  126.39

    Select 
    [name] Like '%coffee Cake%', September, October, November, December
    from [dbo].[Receipts]
    Pivot 
    )
    sum(count(Name)Total
    for Month
    IN ([September], [October], [November], [December])
    )
    AS Pvt_Table

returns error

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'Like'.

CodePudding user response：

If the months can be fixed or permanent, I hope this helps.

 SELECT Name, 
         SUM([1]) AS Jan, 
         SUM([010]) as QtyJan,
         SUM([2]) AS Feb, 
         SUM([20]) as QtyFeb,
         SUM([3]) AS Mar,
         SUM([30]) as QtyMar, 
         SUM([4]) AS Apr, 
         SUM([40]) as QtyApr,
         SUM([5]) AS May, 
         SUM([50]) as QtyMay,
         SUM([6]) AS Jun, 
         SUM([60]) as QtyJun,
         SUM([7]) AS Jul,
         SUM([70]) as QtyJul,
         SUM([8]) AS Aug, 
         SUM([80]) as QtyAug,
         SUM([9]) AS Sep,
         SUM([90]) as QtySep,
         SUM([10]) AS Oct,
         SUM([100]) as QtyOct,
         SUM([11]) AS Nov,
         SUM([110]) as QtyNov,
         SUM([12]) AS Dec, 
         SUM([120]) as QtyDec
         FROM ( SELECT * 
         FROM ( SELECT Name,
                        MONTH(Date) as Month,
                        MONTH(Date)*10 as Month10,
                        SUM(Tax_Exclusive_Price) Total_Tax_Exclusive_Price,
                        COUNT(name) as Qty
                        FROM Receipts GROUP BY Name, MONTH(Date) ) as Tab1
PIVOT ( SUM(Total_Tax_Exclusive_Price) FOR Month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) )  TotalPrice
PIVOT ( SUM(Qty)  FOR Month10 IN ([010], [20], [30], [40], [50], [60], [70], [80], [90], [100], [110], [120]) )  TotalQuantity
) Tab2
GROUP BY Name

CodePudding user response：

Select * From
(
Select DATENAME(Month, [Date])Month, [Name]
From [dbo].[Receipts]
Where [Name] Like 'Coffee Cake%'
and [date] Between '2022-09-01'and '2022-12-25'
)t
Pivot 
(
Count(Name)
For Month
IN ([September],[October],[November], [December])
)
AS Pvt_Table

This gets me pretty close but not quite there yet It returns

|september|October|November|December|
|   150   |  160  |  151   |   119  |