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

      之前一篇讲述了如何将结果集转成一列，下面介绍如何将一个结果集转为多列。

      假设原数据表如图1所示，现在要将如图2显示的结果集转换为如图3所示的结果集。

                 

                                     图1                                                      图2                                             图3

      由图1得到图2的SQL语句为

select ProvinceName,CityName from CityTable

      也许读者可能想到用上一篇文章说的思路来解决将如图2所示的结果集转换为如图3所示的结果集，我们这里不妨试下，执行以下SQL代码

select
case when ProvinceName='福建' then CityName else '' end as '福建',
case when ProvinceName='湖南' then CityName else '' end as '湖南',
case when ProvinceName='浙江' then CityName else '' end as '浙江'
from CityTable

       结果显示为如图4所示

                                                                                      图4

      然后我们再将每个Case返回的结果进行求最大数即求聚集函数MAX(),SQL如下：

select
MAX(case when ProvinceName='福建' then CityName else '' end) as '福建',
MAX(case when ProvinceName='湖南' then CityName else '' end) as '湖南',
MAX(case when ProvinceName='浙江' then CityName else '' end) as '浙江'
from CityTable

执行结果如图5所示

                                                                                       图5

      显然由图4得到图5是因为一个MAX()得出每一列的最大值，字符型数据也有最大值，显然最后结果集显示的只有一行数据，而这明显不是我们要的结果。

      要得到我们想要得到如图3所示的结果，有一种方式是通过SQL的窗口函数来实现，先复制SQL代码给大家看看

with NewTable as(select *,row_number() over(partition by ProvinceName order by CityName) as RN from CityTable)selectMAX(case when ProvinceName='福建' then CityName else '' end) as '福建',MAX(case when ProvinceName='湖南' then CityName else '' end) as '湖南',MAX(case when ProvinceName='浙江' then CityName else '' end) as '浙江',RNfrom NewTablegroup by RN

      接下来讲解下上面这段SQL语句。 

      1~6用了公用表表达式CTE(优点：1、没有像临时表那样耗性能，也没有像子查询那样可读性差。2、增强了可维护性和效率)定义了一个临时数据集(暂且这么描述)，然后在第12行中引用这个公用表数据集，关于CTE大家可以到其他网站再好好了解，这里不再详细说明。同时上面的SQL也涉及到了SQL的窗口函数row_number() over(partition by ProvinceName order By CityName).

      row_number() over (order by  ColumnName)函数是SQL2005在SQL2000的基础上新增的一个函数，作用是根据ColumnName这个列进行排序并编号，编号从1开始。

      举个例子：

select *,
row_number() over( order by CityName)
as RN from CityTable

      执行上面的SQL语句，将结果集按照CityName这个列进行排序，并且编号，编号从1开始，将得到如图6所示的结果集

图6

      而partition by ProvinceName是将整个结果集先根据ProvinceName进行分区(这里是把每个省划分为一个分区)，再对各个不同的分区根据CityName进行排序，并重新编号，编号从1开始。

       关于窗口函数具体请看下一篇文章http://blog.csdn.net/bin_520_yan/archive/2010/10/31/5977571.aspx

       执行下面的SQL语句

select *,
row_number() over(partition by ProvinceName order by CityName)
as RN from CityTable

      执行结果如图7所示

图7

        然后将这个结果集进行类似上一篇文章所述的方式，利用case when else end语句将行转换为列，执行下面SQL语句

with NewTable as
(
select *,
row_number() over(partition by ProvinceName order by CityName)
as RN from CityTable
)
select
 case when ProvinceName='福建' then CityName else '' end as '福建',
 case when ProvinceName='湖南' then CityName else '' end as '湖南',
 case when ProvinceName='浙江' then CityName else '' end as '浙江',
RN
from NewTable

        执行结果如图8所示

图8

        看了上面的结果，大家可能有个想法，可以将RN为相同值的行放在一起，对RN、ProvinceName及CityName进行分组,如下

with NewTable as
(
select *,
row_number() over(partition by ProvinceName order by CityName)
as RN from CityTable
)
select
case when ProvinceName='福建' then CityName else '' end as '福建',
case when ProvinceName='湖南' then CityName else '' end as '湖南',
case when ProvinceName='浙江' then CityName else '' end as '浙江',
RN
from NewTable
group by RN,ProvinceName,CityName

        执行上面的SQL语句将得到如图9所示结果

图9

        可能大家已经明白了接下来该怎么做了，是的，接下来我们求每组的MAX()聚集函数的值、并且只按RN进行分组即可，如下SQL语句

with NewTable as
(
select *,
row_number() over(partition by ProvinceName order by CityName)
as RN from CityTable
)
select
MAX(case when ProvinceName='福建' then CityName else '' end) as '福建',
MAX(case when ProvinceName='湖南' then CityName else '' end) as '湖南',
MAX(case when ProvinceName='浙江' then CityName else '' end) as '浙江',
RN
from NewTable
group by RN

        执行上面的SQL语句就得到我们想要的如图3所示的效果了！