、简单的行转列,数据库中有如下图1所示表CityTable
图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