我目前得到的結果集如下：
我想要做的是讓結果顯示如下：
我整理了以下查詢，我很好奇是否有人有更好的方法：
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
這是示例資料：
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
uj5u.com熱心網友回復：
您可以使用條件聚合。也就是說，聚合函式環繞CASE運算式。
例如...
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/323252.html