我要做一个如下图所示的雷达图:
可是ORCLE查询所得的数据结构如下图所示:
这个格式的数据无法绑定控件,需要转换成如下格式:
这就需要用到oracle11g提供的行列转换函数pivot,unpivot
第一步将列转换为行:
SQL代码如下:
with tmp_orig as (
select xzqdm,label_,value_ from Ba_Bdc_Ywljjg_Nxjg
unpivot(value_ for label_ in(a1,a2,a3,a4,a5,a6,a7)) order by label_)
select * from tmp_orig
再将列转化为行:
全部SQL如下:
with tmp_orig as (
select xzqdm,label_,value_ from Ba_Bdc_Ywljjg_Nxjg
unpivot(value_ for label_ in(a1,a2,a3,a4,a5,a6,a7)) order by label_)
select * from tmp_orig
pivot (
sum(value_)for xzqdm in
(n'320100' as "南京市",n'320200' as "苏州市",n'320300' as "无锡市",
n'320400' as "徐州市",n'320500' as "常州市",n'320600' as "南通市",
n'320700' as "扬州市",n'320800' as "淮安市",n'320900' as "盐城市",
n'321000' as "连云港市",n'321100' as "泰州市",n'321200' as "宿迁市",
n'321300' as "镇江市")
)
|