日期:2014-05-17  浏览次数:20710 次

请问下sql语句!
有张实体表
 姓名 部门 职称
 
张三 IT 员工
李四 销售 员工
王二 策划 经理

查询改写成:

姓名 张三 李四 王二
   
部门 IT 销售 策划

职称 员工 员工 经理


------解决方案--------------------
SQL code

select '姓名' xm,replace(wm_concat(姓名),',',' ') from tb1
union all
select '部门' bm,replace(wm_concat(部门 ),',',' ')  from tb1
union all
select '职称' 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