Converting Multiple Rows to Columns-CodePudding

How would I convert rows into columns? In this case, taking the columns of Reason and Weight and expanding the values where more columns are created?

The column Reason will only have four values (whereas the field may be blank too): Natural, Emissions, and Humans.

Input Table Example:

Location Year   Climate Reason       Weight
USA      2010   Warm    Natural      50.00
USA      2010   Hot     Emissions     0.00
USA      2009   Warm                 20.00
USA      2010   Warm    Humans       30.00
Brazil   2010   Warm    Emissions   100.00

Output Table Example

Location Year   Climate Natural_Weight Emissions_Weight Humans_Weight NULL_Weight
USA      2010   Warm    50.00            0.00           30.00
USA      2010   Hot
USA      2009   Warm                                                  20.00
Brazil   2010   Warm                   100.00

I was thinking of doing a pivot, but got a bit confused since I'm trying to deal with two columns that are dependent on others.

SELECT Location, Year, Climate, Natural_Weight, Emissions_Weight, Humans_Weight, NULL_Weight
FROM   (SELECT Location, Year, Climate, Reason, Weight FROM   Test) src
       PIVOT ( Max (Year)
             FOR Climate IN (Location, Year, Climate)) pvt;

I tried to do a pivot, but I my above query in generating some horrible errors - at the same time, not sure if I'm missing a group by to ensure things are consolidating in the right way. Any guidance would be appreciated!

CodePudding user response：

Perhaps this will help

Select *
 From  ( 
            Select Location
                  ,Year
                  ,Climate
                  ,Item = concat(coalesce(Reason,'NULL'),'_Weight')
                  ,Value = Weight
             From YourTable
       ) src
 Pivot ( sum(Value) for Item in ( [Natural_Weight],[Emissions_Weight],[Humans_Weight],[NULL_Weight] ) ) pvt

Results