用SQL实现结果集的各种转换----将结果集反向转置为一列

      之前的四篇关于用SQL将结果集进行转换的知识点，个人感觉可能就是行转为列时有点伤脑筋，但是找到方法后也还是可以解决的，其中有一点，对于每组中的第N行假设要一起排在结果集中的同一行,那么，那么就设法让每组中的第N行的某个列(假设为RN)的值为相同，其中用窗口函数Row_Number()方法可以实现,这样只要按照RN分组外加聚合函数(如Max)即可实现，然后在Case中判断RN列的值。而对于列转行则直接使用笛卡尔积，使得原表中的每一行(假设有M列)都具有M行，接下来要让这M行可以根据某两个列(其中一个进行分区，另外一个进行Row_Number() over(order by 列名) as RN)，使得每行具有不同的一个RN值，这样就可以根据Case子句进行最后的组合。

      一般情况下我们只需要利用窗口函数(row_number() over (partition by column1 order by column2))和Case子句进行行转列，而利用笛卡尔积和Case子句进行列转行.

      下面介绍一种很特殊的情况，将所有的行转换为一列，将图1所示的结果集转换为图2所示的结果集。     

                               图1                                图2

      根据前几章的介绍，我们这里要将图1的结果集转换为图2的结果集，其实就是将行转为列。这种特殊的行转列，我们用笛卡尔积和case子句即可解决。

      首先将图1中的结果集进行笛卡尔运算，使得每行都变为相应的三行。如下SQL语句：

select ProvinceName,CityName,CityPopulation from CityTable,
(select top 3 ID from CityTable) as Table1

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

图3

       现在我们要将每个ProvinceName进行分区，将每个ProvinceName里的所有CityName进行排序，如下SQL语句

with TableOne as
(
select ProvinceName,CityName,CityPopulation from CityTable,
(select top 3 ID from CityTable) as Table1
)
select *,row_number() over(partition by CityName order by CityName) as RN from TableOne

       执行结果如图4所示

图4

     接下来根据不同的RN值利用Case子句进行判断，即可得到结果

with TableOne as
(
select ProvinceName,CityName,CityPopulation from CityTable,
(select top 3 ID from CityTable) as Table1
)
,TableTwo as
(
select *,row_number() over(partition by CityName order by CityName) as RN from TableOne
)
select
case when RN=1 then ProvinceName
     when RN=2 then CityName
     when RN=3 then cast(CityPopulation as char(10))
     else ''
     end as '结果'
from TableTwo
order by ProvinceName

     执行以上SQL语句即可得到如图5所示的结果

图5

      该过程中遇到一些问题，如下：

      一、错误信息：除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

SQL语句如下：

with TableOne as ( select ProvinceName,CityName,CityPopulation from CityTable, (select top 3 ID from CityTable) as Table1 ) ,TableTwo as ( select *,row_number() over(partition by CityName order by CityName) as RN from TableOne order by ProvinceName ) select case when RN=1 then ProvinceName when RN=2 then CityName when RN=3 then cast(CityPopulation as char(10)) else '' end as '结果' from TableTwo --order by ProvinceName

       将第18行的order by ProvinceName放在了第9行，出错的原因：针对一个表的SELECT其实并不是返回一个表，而是一个游标；作为子查询，如果有order.....，规定必须这个子查询中含有一个top关键字。于是解决办法由两种：

      第一：在该子查询中添加一个top关键字的语句:top 100 percent,即将第8行改为：

select top 100 percent*,row_number() over(partition by CityName order by CityName) as RN from TableOne

      第二：在子查询中删除Order by 语句，将order by语句放在外部查询语句中。

      二、下面的SQL语句中case子句里发生错误，信息如下：在将 nvarchar 值 '湖南' 转换成数据类型 int 时失败。with TableOne as ( select ProvinceName,CityName,CityPopulation from CityTable, (select top 3 ID from CityTable) as Table1 ) ,TableTwo as ( select*,row_number() over(partition by CityName order by CityName) as RN from TableOne ) select case when RN=1 then ProvinceName when RN=2 then CityName when RN=3 then CityPopulation else '' end as '结果' from TableTwo order by ProvinceName

      因为在上面的case子句中，ProvinceName和CityName是字符型的，而CityPopulation的类型是整型，所以要将CityPopulation转换为字符型，即将第13行代码改为when RN=3 then cast(CityPopulation as char(10))即可。