SQL pivot and group by month-CodePudding

I'm trying to get the sum of production per month and pivot the month row to columns. Could you please tell me what is wrong with this code?'

select *
from (
    select datename(month,day), UnitId, sum(pv_production) as production, year(day)
    from MeterReading
    group by datename(month,day), UnitId, year(day))  p
    pivot ( 
        max(pv_production) for datename(month, day) in ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December]
    )
) as pp

CodePudding user response：

There were several items wrong in your query. Columns need aliases and your max() within the PIVOT was referencing an invalid column name

Select *
  From (
        Select [Month] = datename(month,day)
             , [Year]  = year(day)
             , UnitId
             , production = sum(pv_production) 
         From  MeterReading
         Group By datename(month,day)
                 ,year(day)
                 ,UnitId 
       )  Src
 Pivot ( max(production) for [Month] in ([January]
                                        ,[February]
                                        ,[March]
                                        ,[April]
                                        ,[May]
                                        ,[June]
                                        ,[July]
                                        ,[August]
                                        ,[September]
                                        ,[October]
                                        ,[November]
                                        ,[December] ) ) Pvt