求问一个SQL横纵表关联查询的结果

Question

问题举例子如下：实际情况，就是表B的字段CODE经常要根据实际情况增减所以没法直接放在A表里面,
但是对于表A里面的每一条，B表里面关联的记录 数量、CODE 都是固定的。表A 横表结构：CUST_ID   NAME   TEL_NUM   STATE    
1         user1   12345      1
2         user2   54321      1表B纵表结构，CUST_ID 关联表AID   CUST_ID    CODE       VALUE
1      1        ADDR       地址1
2      1        VP_FLAG     1
3      1        ****        A
4      2        ADDR       地址2
5      2        VP_FLAG     0
6      2        ****        B
现在想通过一个SQL查询出这样的一个结果：CUST_ID   NAME   TEL_NUM   STATE    ADDR   VP_FLAG   ****
1         user1   12345      1      地址1    1         A
2         user2   54321      1      地址2    0         B
如果B表里面 CODE 增加一个的话，查询结果会自动增加一列，显示每条记录的该CODE的值网上查了很多，对于纵表转横标都是固定列的求高人解决......

Answer 1

如果B表中的CUST_ID和CODE两列可以唯一决定一行记录，那我的答案可行，****不能作为一列，所以我将查询结果的列明换成了stars
select ta.CUST_ID,ta.NAME,ta.TEL_NUM,ta.STATE,
  (select VALUE from B where ta.CUST_ID = B.CUST_ID and B.CODE='ADDR') ADDR,
  (select VALUE from B where ta.CUST_ID = B.CUST_ID and B.CODE='VP_FLAG') VP_FLAG,
  (select VALUE from B where ta.CUST_ID = B.CUST_ID and B.CODE='****') STARS 
from A ta;
附上建表和插入记录的SQL语句，没有加主外键限制。create table a 
(
  CUST_ID number(20),
  NAME varchar2(20),
  tel_num number(20),
  state number(1)
);create table b
(
  ID number(20),
  CUST_ID number(20),
  CODE varchar2(20),
  VALUE varchar2(20)
);insert into a values(1,'user1',12345,1);
insert into a values(2,'user2',54321,1);insert into b values(1,1,'ADDR','地址1');
insert into b values(2,1,'VP_FLAG','1');
insert into b values(3,1,'****','A');
insert into b values(4,2,'ADDR','地址2');
insert into b values(5,2,'VP_FLAG','0');
insert into b values(6,2,'****','B');

Answer 2

回1楼，很多这种纵转横的都是采用类似的模式写的但是要求是B表的CODE是会增加的，
比如我的例子上是 3个 ADDR、VP_FLAG、***
如果再增加一个编码，那SQL就得重写。之所以做纵表就是为了扩展性方便。
重写SQL就没意义了...

Answer 3

这个代码可以适应你的code变化的要求。
新建一个表存储结果。
给分啊~~~~DECLARE 
  CURSOR c_code IS 
    SELECT DISTINCT code FROM b;
  s_sql VARCHAR2(32767);
  s_sub_sql VARCHAR2(100):= ',(select value from b where ta.CUST_ID = B.CUST_ID and B.CODE=';
  su_sql_create varCHAR2(32767);
BEGIN
  s_sql := 'select ta.CUST_ID,ta.NAME,ta.TEL_NUM,ta.STATE';
  FOR cc IN c_code loop
    s_sql := s_sql || s_sub_sql ||chr(39)||cc.code||chr(39)||') '||cc.code;
  END loop;
  s_sql := s_sql ||' from A ta';
--  dbms_output.put_line(s_sql);
  su_sql_create := 'create table coddde as '||s_sql;
  EXECUTE IMMEDIATE su_sql_create;
exception
  when others then dbms_output.put_line(sqlcode);
END;
/

Answer 4

--类似的问题以前的帖子很多哈，动态SQL行列转换酒行了，用存储过程来做：C:\Documents and Settings\Administrator>sqlplus /nologSQL*Plus: Release 10.2.0.1.0 - Production on 星期四 7月 26 15:07:47 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL> conn aps2/aps2
已连接。
SQL> select * from tab_a;   CUST_ID NAME     TEL_NUM      STATE
---------- ----- ---------- ----------
         1 user1      12345          1
         2 user2      54321          1SQL> select * from tab_b;        ID    CUST_ID CODE    VALUE
---------- ---------- ------- -----
         1          1 ADDR    地址1
         2          1 VP_FLAG 1
         3          1 ****    A
         4          2 ADDR    地址2
         5          2 VP_FLAG 0
         6          2 ****    B已选择6行。SQL> --创建存储过程
SQL> create or replace procedure row_to_col_func(cur out sys_refcursor)
  2  as
  3    sqlstr varchar2(2000):='select a.cust_id,max(a.name) as name,max(a.tel_num) as tel_num,max(a.state) as state ';
  4  begin
  5       for rs in (select distinct code from tab_b ) loop
  6         sqlstr:=sqlstr||chr(10)||','||'max(decode(b.code,'''||rs.code||''',b.value,'''')) as "'||rs.code||'"';
  7       end loop ;
  8       sqlstr:=sqlstr||chr(10)||'from tab_a a,tab_b b WHERE a.cust_id=b.cust_id  group by a.cust_id ' ;
  9       open cur for sqlstr;
 10  end row_to_col_func;
 11  /过程已创建。SQL> --测试
SQL> var cur refcursor
SQL> exec row_to_col_func(:cur);PL/SQL 过程已成功完成。SQL> print cur   CUST_ID NAME     TEL_NUM      STATE VP_FL ADDR  ****
---------- ----- ---------- ---------- ----- ----- -----
         1 user1      12345          1 1     地址1 A
         2 user2      54321          1 0     地址2 BSQL> insert into tab_b values(7,1,'xxx','xxx');已创建 1 行。SQL> commit;提交完成。SQL> exec row_to_col_func(:cur);PL/SQL 过程已成功完成。SQL> print cur   CUST_ID NAME     TEL_NUM      STATE VP_FL ADDR  ****  xxx
---------- ----- ---------- ---------- ----- ----- ----- -----
         1 user1      12345          1 1     地址1 A     xxx
         2 user2      54321          1 0     地址2 BSQL> insert into tab_b values(8,2,'xxx','yyy');已创建 1 行。SQL> commit;提交完成。SQL> exec row_to_col_func(:cur);PL/SQL 过程已成功完成。SQL> print cur   CUST_ID NAME     TEL_NUM      STATE VP_FL ADDR  ****  xxx
---------- ----- ---------- ---------- ----- ----- ----- -----
         1 user1      12345          1 1     地址1 A     xxx
         2 user2      54321          1 0     地址2 B     yyySQL>