SQLTeam.com | Weblogs | Forums

How to extract specific row value into a new column based on shared reference number?

In the below query I have a list of discount values (Discount1) and assigned reference numbers where EffectiveID is a discount reference and BandID is a value reference within the discount.
Some discounts are one level with one discount value assigned, some are two level discounts which share EffectiveID but have unique BandID.

SELECT
Band.EffectiveDiscountID AS EffectiveID,
Band.DiscountBandID AS BandID,
Band.PercentDiscount AS Discount1,
CASE WHEN Discount.Description LIKE '%then%' THEN 'Two Level Discount'
ELSE 'One Level Discount' END AS 'DiscountLevel'
FROM
Discount INNER JOIN
Effective ON Discount.DiscountID = Effective.DiscountID
RIGHT OUTER JOIN
Band ON Effective.EffectiveDiscountID = Band.EffectiveDiscountID

The current output looks like this:
1

What I would like is for two-level discounts the second discount1 value to be listed in the new column Discount2 rather than the row below, for example discount1 value 8.00 for BandID 3 would remain in column discount1 however BandID 4 value 1.00 would be listed in new column Discount2, see below:

2

I would appreciate any ideas on how to achieve this.
Thanks.