Phil Parkin - Wednesday, August 16, 2017 2:24 PM

Something like this should get you most of the way there:

WITH Results
AS
(
  SELECT
    UPPERAGE
  ,  [First trail year] = CASE
            WHEN DurationLower <= 2 THEN
             DurationLower
            ELSE
             NULL
           END
  ,  [Next trail year] = CASE
            WHEN DurationLower >= 3 THEN
             DurationLower
            ELSE
             NULL
           END
  ,  [First trail %]  = CASE
            WHEN DurationLower <= 2 THEN
             Rate / 100
            ELSE
             NULL
           END
  ,  [Next trail %]  = CASE
            WHEN DurationLower >= 3 THEN
             Rate / 100
            ELSE
             NULL
           END
  FROM
    Pic.PageToProduct_VW    pp
  LEFT JOIN Pic.PageToProductToRate_vw ppr ON ppr.PageToProductID = pp.PageToProductID
  LEFT JOIN Pic.ProductRates_VW   r ON ppr.ProductRatesID  = r.ProductRatesID
  WHERE pp.PageID = '76A1'
)
SELECT
   UPPERAGE
,   MAX(Results.[First trail year])
,   MAX(Results.[Next trail year])
,   MAX(Results.[First trail %])
,   MAX(Results.[Next trail %])
FROM  Results
GROUP BY UPPERAGE;