New to SQL, struggling to fully understand the pivot clause. I have four fields (state, season, rain, snow) and am trying to pivot so that I have 5 fields (state, summer_rain, summer_snow, winter_rain, winter_snow). I'm not sure how to pivot two fields so that they are prefixed with another if that makes sense. Reprex below.

What I have now

What I'm after

My code (receiving an error when aggregating snow & rain within pivot clause):

DECLARE @myTable AS TABLE([state] VARCHAR(20), [season] VARCHAR(20), [rain] int, [snow] int)
INSERT INTO @myTable VALUES ('AL', 'summer', 1, 1)
INSERT INTO @myTable VALUES ('AK', 'summer', 3, 3)
INSERT INTO @myTable VALUES ('AZ', 'summer', 0, 1)
INSERT INTO @myTable VALUES ('AL', 'winter', 5, 4)
INSERT INTO @myTable VALUES ('AK', 'winter', 2, 2)
INSERT INTO @myTable VALUES ('AZ', 'winter', 1, 1)
INSERT INTO @myTable VALUES ('AL', 'summer', 6, 4)
INSERT INTO @myTable VALUES ('AK', 'summer', 3, 0)
INSERT INTO @myTable VALUES ('AZ', 'summer', 5, 1)

SELECT [state], [year], [month], [day]
FROM
(
      SELECT * FROM @myTable
) t
PIVOT
(
    sum([rain]), sum([snow]) FOR [season] IN ([summer], [winter]) 
) AS pvt

CodePudding user response：

PIVOTS are great, but Conditional Aggregations offer a bit more flexibility and often more performant.

PIVOT

Select *
 From  (
            SELECT State 
                  ,B.*
             FROM  @myTable
             Cross Apply (values (concat(season,'_rain'),rain)
                                ,(concat(season,'_snow'),snow)
                         ) B(Item,Value)
       ) src
  Pivot ( sum(value) for Item in ([summer_rain],[summer_snow],[winter_rain],[winter_snow]) ) pvt

Conditional Aggregation

Select State
      ,[summer_rain] = sum(case when season='summer' then rain end)
      ,[summer_snow] = sum(case when season='summer' then snow end)
      ,[winter_rain] = sum(case when season='winter' then rain end)
      ,[winter_snow] = sum(case when season='winter' then snow end)
 From  @myTable
 Group By State