Pivot table to make all values appear in same row, multiple columns

I have the following query

SELECT u.Unit, ut.Month, ut.Num , ut.Denom
FROM Unit u
INNER JOIN UnitTest ut ON u.Id = ut.UnitId

This gives the following results:

Unit    Month    Num   Denom
1       March    123    50
1       April    325    60
1       May      653    59
2       March    656    68
2       April    469    98

And many more units going from month jan - dec.

how would I get the data to display as so:

Unit  Jan   Feb   March  April  May June....
1                  2.46   5.41..............
2      .....................................

Where it shows the month (Always Jan - Dec, and the year does not matter as it is already filtered) as headers and shows the res开发者_开发知识库pective divided calculation from the numerator and denominator?

Something like this should work. I've used SUM() to aggregate, but you might want to use AVG or MIN or MAX depending on your business logic.

SELECT
    Unit,
    SUM(CASE Month WHEN 'Jan' THEN CAST(Num AS DECIMAL(10, 2))/Denom ELSE 0 END),
    SUM(CASE Month WHEN 'Feb' THEN CAST(Num AS DECIMAL(10, 2))/Denom ELSE 0 END),
    ...
FROM
    Unit U
INNER JOIN UnitTest UT ON
    U.Id = UT.UnitId
GROUP BY
    Unit

Also, you might want to include your table aliases when using your columns in the query. It makes things much clearer.

If using the PIVOT operator you need to do the Num/Denom in a CTE/ derived table that is then pivoted.

WITH T
     AS (SELECT Unit,
                [Month],
                CAST(Num AS FLOAT) / Denom AS Val
         FROM   Unit u
                INNER JOIN UnitTest ut
                  ON u.Id = ut.UnitId)
SELECT *
FROM   T PIVOT (MAX(Val) FOR [Month] IN ([March], [April] /*...*/) ) AS PVT