求一个行列转行的SQL

Question

已知用户表:
create table user(uid int,uname varchar2(10));
邮箱表：（一个用户可能有几个邮箱）create table email(eid int,uid int,email_address varchar2(50));希望最后得到uid   name   email_address1, email_address2(如果该用户有几个邮箱的话)

Answer 1

1、不要使用UID做为列名，uid为ORACLE函数。用作列名会提示错误。
2、email_address个数不定，需要写一存储过程，然后使用动态SQL拼接来完成。

Answer 2

楼主，你的建表语句某些名称为ORACLE关键字，不能使用，修改你的建表语句如下：--创建user表
create table t_user(ID int,uname varchar2(10));
insert into t_user (ID, UNAME)
values (1, 'Tom');
--插入测试数据
insert into t_user (ID, UNAME)
values (2, 'Jack');insert into t_user (ID, UNAME)
values (3, 'Smith');
--创建email表
CREATE TABLE t_email(id int,user_id int,email_address varchar2(50));
--插入测试数据
insert into t_email (ID, USER_ID, EMAIL_ADDRESS)
values (1, 1, '[email protected]');insert into t_email (ID, USER_ID, EMAIL_ADDRESS)
values (2, 2, '[email protected]');insert into t_email (ID, USER_ID, EMAIL_ADDRESS)
values (3, 3, '[email protected]');insert into t_email (ID, USER_ID, EMAIL_ADDRESS)
values (4, 1, '[email protected]');--根据楼主要求，sql如下：
SELECT *
FROM (SELECT a.id user_id,
             a.uname NAME,
             MAX(DECODE(b.ID, 1, email_address)) email_address1,
             MAX(DECODE(b.ID, 4, email_address)) email_address2
      FROM t_user a, t_email b
      WHERE a.id = b.user_id
      GROUP BY a.id, a.uname
      UNION
      SELECT a.id user_id,
             a.uname NAME,
             MAX(DECODE(b.ID, 2, email_address)) email_address1,
             MAX(DECODE(b.ID, 3, email_address)) email_address2
      FROM t_user a, t_email b
      WHERE a.id = b.user_id
      GROUP BY a.id, a.uname) t
WHERE length(t.email_address1 || t.email_address2) <> 0
--结果集如下：
USER_ID NAME EMAIL_ADDRESS1 EMAIL_ADDRESS2
1 Tom [email protected] [email protected]
2 Jack [email protected]
3 Smith [email protected]以上代码本机测试通过，希望会对你有所帮助。

Answer 3

提供的是一个思路，若是扩展的话，可以考虑用游标for循环执行动态sql，把id作为变量来解决大数据量列转行问题，你可以对每一行判断id和name是否有重复，然后拼装动态sql，这个sql的基本格式可以套用我写的那个。
不同的人有不同的解决方案，我说的只是我自己的想法，实现起来也许有诸多问题，那就需要自己上网查资料了。

Answer 4

这个方法的确不错，但是当email_id 很多的时候，每一行的值就会很多 很长，看起来不方便

Answer 5

groups@SZTYORA> create table user(uid int,uname varchar2(10));
create table user(uid int,uname varchar2(10))
             *
第 1 行出现错误:
ORA-00903: invalid table name-- user 、uid 都是oracle的保留关键字！

Answer 6

试试这样能否达到你的要求
select a.nid,a.uname,wm_concat(b.addr) 
from (select 1 nid,'abc' uname from dual) a,
(
select 1 eid,1 nid,'abc@mail' addr from dual
union all
select 2 eid,1 nid,'abc02@mail' addr from dual
) b where a.nid=b.nid

Answer 7

最终还是自己想了个其他方法解决，和大家分享下，希望多拍砖CREATE OR REPLACE PROCEDURE p_tel_info_t(view_name_start     VARCHAR2,
                                         view_name_end       VARCHAR2)AS  sqlstr  VARCHAR2(1000) := 'create or replace view ' || view_name_start || ' as select userid,wmsys.wm_concat(telnumber) telnumber from test group by userid'; 
  v1      NUMBER;
  v2      NUMBER :=5;
  v_sql VARCHAR2(1000) := 'create or replace view ' || view_name_end || ' as ';
  v_sql_1 VARCHAR2(2048) := 'create or replace view ' || view_name_end || ' as SELECT userid,
                       substr(telnumber,1,decode(instr(telnumber,'','',1,1),0,12,12)-1) tel1,
                       CASE WHEN instr(telnumber,'','',1,1)=0 THEN NULL
                            WHEN instr(telnumber,'','',1,2)=0 THEN substr(telnumber,instr(telnumber,'','',1)+1)
                            ELSE substr(telnumber,instr(tel,'','',1,1)+1,instr(telnumber,'','',1,2)-instr(telnumber,'','',1,1)-1) END tel2'; 
 
BEGIN
   EXECUTE IMMEDIATE sqlstr;
   SELECT MAX(LENGTHB(wmsys.wm_concat(telnumber))-LENGTHB(REPLACE(wmsys.wm_concat(telnumber),',',''))) INTO v1
   FROM tel_info_t 
   GROUP BY userid;
   v1 :=v1 +1;
   IF v1=1 THEN 
     v_sql := 'create or replace ' || view_name_end || 'AS SELECT userid,telnumber FROM ' || view_name_start; 
     EXECUTE IMMEDIATE v_sql;   
   ELSIF v1=2 THEN
     v_sql_1 := v_sql_1 || ' from ' || view_name_start;
     EXECUTE IMMEDIATE v_sql_1;   
   ELSE 
        FOR v2 IN 3..v1 LOOP
              IF v2 < v1 THEN 
                 v_sql_1 :=v_sql_1 || ', case when instr(telnumber,'','',1,' || v2 ||'-1)=0 then null
                                         else substr(tel,instr(telnumber,'','',1,' || v2 || '-1)+1,
                                         instr(telnumber,'','',1,'|| v2 || ')-instr(telnumber,'','',1,' || v2 || '-1)-1) END tel' || v2;  
              ELSE 
                  v_sql_1 := v_sql_1 || ', CASE WHEN instr(telnumber,'','',1,' || v2 || '-1)=0 THEN NULL 
                                           ELSE substr(telnumber,instr(telnumber,'','',1,' || v2 || '-1)+1) END tel' || v2;                           
              END IF;                                    
         END LOOP;    
      v_sql_1 := v_sql_1 || ' from ' || view_name_start || ';';
   EXECUTE IMMEDIATE v_sql_1;      
   END IF;    
 
END;     

Answer 8

刚才那个有个地方错了create table test (tid number,userid number,telnumber number);
CREATE OR REPLACE PROCEDURE p_tel_info_t(view_name_start     VARCHAR2,
                                         view_name_end       VARCHAR2)AS  sqlstr  VARCHAR2(1000) := 'create or replace view ' || view_name_start || ' as select userid,wmsys.wm_concat(telnumber) telnumber from test group by userid'; 
  v1      NUMBER;
  v2      NUMBER :=3;
  v_sql VARCHAR2(1000) := 'create or replace view ' || view_name_end || ' as ';
  v_sql_1 VARCHAR2(2048) := 'create or replace view ' || view_name_end || ' as SELECT userid,
                       substr(telnumber,1,decode(instr(telnumber,'','',1,1),0,12,12)-1) tel1,
                       CASE WHEN instr(telnumber,'','',1,1)=0 THEN NULL
                            WHEN instr(telnumber,'','',1,2)=0 THEN substr(telnumber,instr(telnumber,'','',1)+1)
                            ELSE substr(telnumber,instr(tel,'','',1,1)+1,instr(telnumber,'','',1,2)-instr(telnumber,'','',1,1)-1) END tel2'; 
 
BEGIN
   EXECUTE IMMEDIATE sqlstr;
   SELECT MAX(LENGTHB(wmsys.wm_concat(telnumber))-LENGTHB(REPLACE(wmsys.wm_concat(telnumber),',',''))) INTO v1
   FROM tel_info_t 
   GROUP BY userid;
   v1 :=v1 +1;
   IF v1=1 THEN 
     v_sql := 'create or replace ' || view_name_end || 'AS SELECT userid,telnumber FROM ' || view_name_start; 
     EXECUTE IMMEDIATE v_sql;   
   ELSIF v1=2 THEN
     v_sql_1 := v_sql_1 || ' from ' || view_name_start;
     EXECUTE IMMEDIATE v_sql_1;   
   ELSE 
        FOR v2 IN 3..v1 LOOP
              IF v2 < v1 THEN 
                 v_sql_1 :=v_sql_1 || ', case when instr(telnumber,'','',1,' || v2 ||'-1)=0 then null
                                         else substr(tel,instr(telnumber,'','',1,' || v2 || '-1)+1,
                                         instr(telnumber,'','',1,'|| v2 || ')-instr(telnumber,'','',1,' || v2 || '-1)-1) END tel' || v2;  
              ELSE 
                  v_sql_1 := v_sql_1 || ', CASE WHEN instr(telnumber,'','',1,' || v2 || '-1)=0 THEN NULL 
                                           ELSE substr(telnumber,instr(telnumber,'','',1,' || v2 || '-1)+1) END tel' || v2;                           
              END IF;                                    
         END LOOP;    
      v_sql_1 := v_sql_1 || ' from ' || view_name_start || ';';
   EXECUTE IMMEDIATE v_sql_1;      
   END IF;    
 
END;