比如:
A表列:
COL1 COL2 COL3
A101 AAA BBBB表列:
COL1 COL4
A101 CCC
A101 DDD
A101 EEE现在我想变成这样子查询出来:COL1 COL2 COL3 COL_1 COL_2 COL_3
A101 AAA BBB CCC DDD EEE就是说把B表COL4的列的几个内容,和A边那条记录一起,变成新的几个列,B表几条记录,那么A表就增加几列,A表的COL1和B表COL1内容一样,可作为WHERE条件,请问SQL语句该怎么写?
A表列:
COL1 COL2 COL3
A101 AAA BBBB表列:
COL1 COL4
A101 CCC
A101 DDD
A101 EEE现在我想变成这样子查询出来:COL1 COL2 COL3 COL_1 COL_2 COL_3
A101 AAA BBB CCC DDD EEE就是说把B表COL4的列的几个内容,和A边那条记录一起,变成新的几个列,B表几条记录,那么A表就增加几列,A表的COL1和B表COL1内容一样,可作为WHERE条件,请问SQL语句该怎么写?
decode(COL4,'DDD',col4,0) COL_2,decode(COL4,'EEE',col4,0) COL_3
from a,b
where a.COL1=b.COL1
;
SQL> select * from table1; ID F1 F2 F3
------- ---------- ---------- ----------
1 a b cSQL> select * from test; ID NAME
---------- --------------------
1 aaa
1 bbb
1 cccSQL>
SQL> select a.*, b.col1, b.col2, b.col3
2 from table1 a,
3 (select id,
4 max(decode(rn, 1, name, null)) col1,
5 max(decode(rn, 2, name, null)) col2,
6 max(decode(rn, 3, name, null)) col3
7 from (select id, name, row_number() over(order by name) RN
8 from test)
9 group by id) b
10 where a.id = b.id
11 / ID F1 F2 F3 COL1 COL2 COL3
---------- ---------- ---------- ---------- ------------ ----------- -----------
1 a b c aaa bbb cccSQL>