关于将结果集转换成列,我们使用窗口函数以及Case子句进行配合,而将结果集转成一行或者多行,则要使用到Case子句和笛卡尔积。
如下图所示
图1 表A 图2 表B 图3
问题:如何根据表A和表B得到图3所示的结果集,首先求表A和表B的笛卡尔积,如下
with NewTable as
(
select * from
(select * from )B as TableB,
(select ProvinceName from A) as TableA
group by 福建,湖南,浙江,ProvinceName
)
执行上面SQL语句得
图4
然后使用case语句取得表B中每个ProvinceName的值进行判断,得到相应的CityName
with NewTable as
(
select * from
(select * from )B as TableB,
(select ProvinceName from A) as TableA
group by 福建,湖南,浙江,ProvinceName
),
LastTable as
(
select
ProvinceName,
case
when ProvinceName='福建' then 福建
when ProvinceName='湖南' then 湖南
when ProvinceName='浙江' then 浙江
else ''
end as CityName
from NewTable
)
select * from LastTable where CityName<>''
group by ProvinceName,CityName
执行上面的SQL语句得到如图5所示结果集
图5
而对于将结果集中一列转换为多行方法也和上面的差不多,再次不在详细说明。