Oracle中把列值转换为列名

最新推荐文章于 2025-05-23 21:04:26 发布

lv941002

最新推荐文章于 2025-05-23 21:04:26 发布

阅读量5.7k

点赞数 2

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/lv941002/article/details/84841542

本文介绍了一种使用Oracle PL/SQL存储过程动态生成SQL查询的方法，该查询能够将表中的特定列值转化为列名，实现数据的宽格式转换。通过遍历目标列的所有唯一值，构建了一个包含多个CASE WHEN语句的SQL查询，用于按接收者ID（acptnum）分组并获取每个问题的最大答案。此外，还提供了一个存储过程示例，展示了如何执行生成的SQL查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table ass(acptnum varchar(30),qu_name varchar(4000),awname varchar(2000));


insert into ass values('15101232097','问题1','答案1');
insert into ass values('15101232097','问题2','答案2');
insert into ass values('15101232097','问题3','答案3');
insert into ass values('18290890601','x','x');
insert into ass values('18290890601','x1','x2');
insert into ass values('18290890601','x2','x2');
insert into ass values('18290890601','x3','x3');

select * from ass;
  acptnum     qu_name   awname                  
1	15101232097	问题1	    答案1
2	15101232097	问题2	    答案2
3	15101232097	问题3	    答案3
4	18290890601	x	        x
5	18290890601	x1	      x2
6	18290890601	x2	      x2
7	18290890601	x3	      x3


declare
c1    sys_refcursor;
v_sql VARCHAR(1000):='';
c2 ass.qu_name%type;
BEGIN
       v_sql:='select acptnum';
       open c1 for select distinct qu_name from ass;
       loop
         fetch c1 into c2;
         exit when c1%notfound;
         v_sql :=v_sql||',max(case qu_name when  '''||c2||'''   then  awname else null end ) as ' ||c2;
       end loop;
      close c1;
      v_sql :=v_sql|| ' from ass group by acptnum';
      dbms_output.put_line(v_sql);   
  END;   


#运行以上代码生成一下SQL,然后放在存储过程里面执行这个sql。然后输出
select acptnum,
       max(case qu_name when  '问题1'   then  awname else null end ) as 问题1,
       max(case qu_name when  '问题2'   then  awname else null end ) as 问题2,
       max(case qu_name when  '问题3'   then  awname else null end ) as 问题3,
       max(case qu_name when  'x1'   then  awname else null end ) as x1,
       max(case qu_name when  'x3'   then  awname else null end ) as x3,
       max(case qu_name when  'x2'   then  awname else null end ) as x2,
       max(case qu_name when  'x'   then  awname else null end ) as x 
    from ass group by acptnum



#以下代码生成sql	 
         
create or replace procedure exeute_select(v_out out sys_refcursor)
as
c1    sys_refcursor;
v_sql VARCHAR(1000):='';
c2 ass.qu_name%type;
BEGIN
       v_sql:='select acptnum';
       open c1 for select distinct qu_name from ass;
       loop
         fetch c1 into c2;
         
         exit when c1%notfound;
         
         v_sql :=v_sql||',max(case qu_name when  '''||c2||'''   then  awname else null end ) as ' ||c2;
         
       end loop;
         close c1;
         
           v_sql :=v_sql|| ' from ass group by acptnum';
           
           open v_out for v_sql;           
  END;

运行结果,将问题列的值转换为列名了。