Pivoting but handling Column Names

I’m currently getting a result-set back as follows:

What I’m trying to do is get the results to appear as follows:

I’ve put together the following query, and I was curious as to whether someone had a better way of doing it:

 select distinct
    t1.Area,
    t2.MedianCurrentYear,
    t2.MedianPreviouYear,
    t2.Difference,
    t3.MedianCurrentYear,
    t3.MedianPreviouYear,
    t3.Difference,
    t4.MedianCurrentYear,
    t4.MedianPreviouYear,
    t4.Difference
 from #temp as t1
 left join #temp as t2 on t1.Area = T2.Area and T2.NumberOfBedrooms = 2
 left join #temp as t3 on t1.Area = T3.Area and T3.NumberOfBedrooms = 3
 left join #temp as t4 on t1.Area = T4.Area and T4.NumberOfBedrooms = 4

Here’s the sample data:

Create Table #temp
(
    Area varchar(50),
    NumberOfBedrooms int,
    MedianCurrentYear money,
    MedianPreviouYear money,
    Difference money
)

insert into #temp
(
    Area,
    NumberOfBedrooms,
    MedianCurrentYear,
    MedianPreviouYear,
    Difference 
)
select
    'Area1',
    2,  
    370,
    365,
    5
union all
select
    'Area1',    
    3,
    406,
    408,    
    -2
union all
select
    'Area1',
    4,
    520,    
    520,
    0
union all
select
    'Area2',    
    2,
    300,
    280,    
    20
union all
select
    'Area2',
    3,  
    406,    
    408,
    -2
union all
select
    'Area2',    
    4,  
    520,    
    520,
    0

Answer

You can use conditional aggregation. That is, aggregate functions wrapped around CASE expressions.

For example…

SELECT
  Area,

  MAX(CASE WHEN NumberOfBedrooms = 2 THEN MedianCurrentYear END)   AS MedianCurrentYear_2Bedroom,
  MAX(CASE WHEN NumberOfBedrooms = 3 THEN MedianCurrentYear END)   AS MedianCurrentYear_3Bedroom,
  MAX(CASE WHEN NumberOfBedrooms = 4 THEN MedianCurrentYear END)   AS MedianCurrentYear_4Bedroom,

  MAX(CASE WHEN NumberOfBedrooms = 2 THEN MedianPreviouYear END)   AS MedianPreviouYear_2Bedroom,
  MAX(CASE WHEN NumberOfBedrooms = 3 THEN MedianPreviouYear END)   AS MedianPreviouYear_3Bedroom,
  MAX(CASE WHEN NumberOfBedrooms = 4 THEN MedianPreviouYear END)   AS MedianPreviouYear_4Bedroom,

  MAX(CASE WHEN NumberOfBedrooms = 2 THEN Difference END)   AS Difference_2Bedroom,
  MAX(CASE WHEN NumberOfBedrooms = 3 THEN Difference END)   AS Difference_3Bedroom,
  MAX(CASE WHEN NumberOfBedrooms = 4 THEN Difference END)   AS Difference_4Bedroom 
FROM
  #temp
GROUP BY
  Area