有张实体表
姓名 部门 职称
张三 IT 员工
李四 销售 员工
王二 策划 经理
查询改写成：
姓名 张三 李四 王二
部门 IT 销售 策划
职称 员工 员工 经理
------解决方案--------------------
- SQL code
select '姓名' xm,replace(wm_concat(姓名),',',' ') from tb1union allselect '部门' bm,replace(wm_concat(部门 ),',',' ') from tb1union allselect '职称' zc,replace(wm_concat(职称),',',' ') from tb1
------解决方案--------------------
- SQL code
create table mdsys.test_20120220_1("姓名" char(10),"部门" char(10),"职称" char(10));insert into mdsys.test_20120220_1 values('张三','IT','员工');insert into mdsys.test_20120220_1 values('李四','销售','员工');insert into mdsys.test_20120220_1 values('王二','策划','经理');select * from mdsys.test_20120220_1;方法一：select *from (select '部门' as "姓名",max(case when "姓名"='张三'then "部门" end)"张三",max(case when "姓名"='李四'then "部门" end)"李四",max(case when "姓名"='王二'then "部门" end)"王二" from mdsys.test_20120220_1 union all select '职称' as "姓名",max(case when "职称"='员工' and "姓名"='张三'then "职称" end)"张三",max(case when "职称"='员工' and "姓名"='李四'then "职称" end)"李四",max(case when "职称"='经理' then "职称" end)"王二" from mdsys.test_20120220_1 )A 方法二： select case when n=1 then '部门' when n=2 then '职称' end"姓名" ,max(case when n=1 and "姓名"='张三' then "部门" when n=2 and "姓名"='张三' then "职称" end)"张三" ,max(case when n=1 and "姓名"='李四' then "部门" when n=2 and "姓名"='李四' then "职称" end)"李四" ,max(case when n=1 and "姓名"='王二' then "部门" when n=2 and "姓名"='王二' then "职称" end)"王二" from mdsys.test_20120220_1,(select level n from dual connect by level<=2) group by case when n=1 then '部门' when n=2 then '职称' end