用SQL实现结果集的各种转换----结果集转成一列

时　间:2012-07-12 15:29:00
作　者:赵文斌   ID：4506  城市：深圳
摘　要:小技巧
正　文:

图1

           现在要将如图2所示的数据格式转换为如图3所示的数据格式

                                                      图2                                                            图3

           这个问题相信很多人都遇到过，首先根据Case语句可以得到如图4所示的数据格式  

select

          case when CityName='南平' then CityPopulation else 0 end as '南平',

          case when CityName='三明' then CityPopulation else 0 end as '三明',

          case when CityName='泉州' then CityPopulation else 0 end as '泉州'

from CityTable

图4

           再在每个Case中加一个聚集函数MAX()即可得到如图3所示的效果

select MAX(case when CityName='南平' then CityPopulation else 0 end)as '南平',

          MAX(case when CityName='三明' then CityPopulation else 0 end)as '三明',

          MAX(case when CityName='泉州' then CityPopulation else 0 end)as '泉州'

from CityTable