例如查出来的数据为
A 2008 1
A 2009 2
A 2010 3
B 2008 4
B 2009 5
B 2010 6
C 2008 7
C 2009 8
C 2010 9怎么给变成下面的累死数据,2008,2009,2010,为新的列
2008 2009 2010
A 1 2 3
B 4 5 6
C 7 8 9
这个怎么实现啊 啊啊啊啊
求高手解答~~~~~~
A 2008 1
A 2009 2
A 2010 3
B 2008 4
B 2009 5
B 2010 6
C 2008 7
C 2009 8
C 2010 9怎么给变成下面的累死数据,2008,2009,2010,为新的列
2008 2009 2010
A 1 2 3
B 4 5 6
C 7 8 9
这个怎么实现啊 啊啊啊啊
求高手解答~~~~~~
SQL> with t as(
2 select 'A' id,'2008' dt,1 nm from dual union all
3 select 'A','2009',2 from dual union all
4 select 'A','2010',3 from dual union all
5 select 'B','2008',4 from dual union all
6 select 'B','2009',5 from dual union all
7 select 'B','2010',6 from dual union all
8 select 'C','2008',7 from dual union all
9 select 'C','2009',8 from dual union all
10 select 'C','2010',9 from dual)
11 select id,
12 max(case dt when '2008' then nm end) "2008",
13 max(case dt when '2009' then nm end) "2009",
14 max(case dt when '2010' then nm end) "2010"
15 from t
16 group by id
17 /
ID 2008 2009 2010
-- ---------- ---------- ----------
A 1 2 3
B 4 5 6
C 7 8 9
--decode()看起来比较舒服
select id
,max(decode(dt,'2008',nm)) "2008"
,max(decode(dt,'2009',nm)) "2009"
,max(decode(dt,'2010',nm)) "2010"
from t
group by id
/
ID 2008 2009 2010
-- ---------- ---------- ----------
A 1 2 3
B 4 5 6
C 7 8 9动态的话,参考:
http://topic.csdn.net/u/20091018/15/7c6729b6-79d1-492e-862d-af9c4921f172.html?91738
不知道这样注释够清楚了没
--创建过程
create or replace procedure proc
as
sqlstr varchar2(2000):='create or replace view v_tmp as select "UNION"'; --创建或者重写视图v_tmp,先写上查询一个字段"UNION"
begin
for cur1 in (select distinct sex from tab1) --把tbal1中所有不相同的sex集合找出来,然后循环
loop
sqlstr:=sqlstr||'
,count(decode(sex,'||cur1.sex||',1))"'||cur1.sex||'"'; --把sex拼凑到那个视图的SQL中,查询出不同sex下的UNION哪一行记录的值
end loop;
sqlstr:=sqlstr||' from tab1 group by "UNION"'; --接上查询tab1和group by 字段
execute immediate sqlstr;
end;
--调用
begin
proc;
end;
--查看结果
select * from v_tmp
不知道这样注释够清楚了没SQL code--创建过程
create or replace procedure proc
as
sqlstr varchar2(2000):='create or replace view v_tmp as……
[/Quote]谢谢,谢谢,这下看懂了,太感谢了