大家好,请问我现在有表A,结构如下:
A:
name type price
a 1 8
a 2 9
a 3 10
b 1 20
b 2 23
b 3 25
我现想,查出结果如下:
a 1 8 2 9 3 10
b 1 20 2 23 3 25
请问,这SQL要怎样写,也就是说要把之前的多行记录,现在按name显示到一行来。
A:
name type price
a 1 8
a 2 9
a 3 10
b 1 20
b 2 23
b 3 25
我现想,查出结果如下:
a 1 8 2 9 3 10
b 1 20 2 23 3 25
请问,这SQL要怎样写,也就是说要把之前的多行记录,现在按name显示到一行来。
2 select 'a' name,1 type,8 price from dual
3 union all
4 select 'a',2,9 from dual union all
5 select 'a',3,10 from dual union all
6 select 'b',1,20 from dual union all
7 select 'b',2,23 from dual union all
8 select 'b',3,25 from dual)
9 select name,replace(wm_concat(type||' '||price),',',' ') newcol
10 from tb
11* group by name
SQL> /N NEWCOL
- --------------------
a 1 8 2 9 3 10
b 1 20 2 23 3 25
2 select 'a' name,1 type,8 price from dual union all
3 select 'a',2,9 from dual union all
4 select 'a',3,10 from dual union all
5 select 'b',1,20 from dual union all
6 select 'b',2,23 from dual union all
7 select 'b',3,25 from dual)
8 select name,max(nel_col) nel_col
9 from (select name,trim(replace(sys_connect_by_path(news,','),',',' ')) nel_col
10 from (select name,type||' '||price news,row_number() over(partition by name order by type) rn
11 from tb)
12 connect by rn= prior rn-1 and connect_by_root(name)=name)
13 group by name
14 /
NAME NEL_COL
---- --------------------------------------------------------------------------------
a 3 10 2 9 1 8
b 3 25 2 23 1 20
--or 10g推荐
SQL>
SQL> with tb as(
2 select 'a' name,1 type,8 price from dual union all
3 select 'a',2,9 from dual union all
4 select 'a',3,10 from dual union all
5 select 'b',1,20 from dual union all
6 select 'b',2,23 from dual union all
7 select 'b',3,25 from dual)
8 select name,replace(wm_concat(type||' '||price),',',' ') from tb group by name
9 /
NAME REPLACE(WM_CONCAT(TYPE||''||PR
---- --------------------------------------------------------------------------------
a 1 8 2 9 3 10
b 1 20 2 23 3 25
可以是可以,但是这样动态行列转换比较难,最好写存储过程去做,可以参考我们版主的精华:http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html?13985