查询语句如下:
select tco.company_name 机构名称,tco.organ_id 机构代码 from t_company_organ tco
结果如下:机构名称 机构代码
上海 101
北京 102
天津 103
湖北 104
湖南 105现在打算得到如下结果:上海 北京 天津 湖北 湖南
101 102 103 104 105不知道查询语句如何写??
select tco.company_name 机构名称,tco.organ_id 机构代码 from t_company_organ tco
结果如下:机构名称 机构代码
上海 101
北京 102
天津 103
湖北 104
湖南 105现在打算得到如下结果:上海 北京 天津 湖北 湖南
101 102 103 104 105不知道查询语句如何写??
SELECT decode(company_name,'上海',organ_id),
decode(company_name,'北京',organ_id),
decode(company_name,'天津',organ_id),
decode(company_name,'湖北',organ_id),
decode(company_name,'湖南',organ_id)
FROM t_company_organ ;
2 张三 数学 86
3 张三 英语 75
4 李四 语文 78
5 李四 数学 85
6 李四 英语 78想变成下面记录:名字 语文 数学 英语1 李四 78 85 78
2 张三 80 86 75----------------------------------------------CREATE TABLE ROW2COLUMN2(NAME_ VARCHAR2(20),
CLASS VARCHAR2(50),
SCORE NUMBER(3));INSERT INTO ROW2COLUMN2 VALUES ('张三', '语文', 80);
INSERT INTO ROW2COLUMN2 VALUES ('张三', '数学', 86);
INSERT INTO ROW2COLUMN2 VALUES ('张三', '英语', 75);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '语文', 78);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '数学', 85);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '英语', 78);SELECT * FROM ROW2COLUMN2;SELECT NAME_,
MAX(DECODE(CLASS, '语文', T.SCORE, 0)) 语文,
MAX(DECODE(CLASS, '数学', T.SCORE, 0)) 数学,
MAX(DECODE(CLASS, '英语', T.SCORE, 0)) 英语
FROM ROW2COLUMN2 T
GROUP BY NAME_;
不知道还有没有其他的方式来实现??
(select organ_id from t_company_organ where company_name='上海' and rownum = 1) as "上海"
(select organ_id from t_company_organ where company_name='北京' and rownum = 1) as "北京"
(select organ_id from t_company_organ where company_name='天津' and rownum = 1) as "天津"
(select organ_id from t_company_organ where company_name='湖北' and rownum = 1) as "湖北"
(select organ_id from t_company_organ where company_name='湖南' and rownum = 1) as "湖南"
from dual;
---------- --------------------
101 上海
102 北京
103 天津
104 湖北
105 湖南scott@ORA1> create or replace procedure p_test(p out sys_refcursor)
2 as
3 v_sql varchar2(32767);
4 begin
5 v_sql := 'select ';
6 for v_rec in (select * from t) loop
7 v_sql := v_sql || ' ( select id from t where name=''' || v_rec.name || ''' and rownum = 1) as " ' || v_rec.name || ' ",';
8 end loop;
9 v_sql := rtrim(v_sql, ',');
10 v_sql := v_sql || ' from dual';
11 open p for v_sql;
12 dbms_output.put_line(v_sql);
13 end;
14 /过程已创建。scott@ORA1> create or replace procedure p_test2(p out sys_refcursor)
2 as
3 v_sql varchar2(32767);
4 begin
5 v_sql := 'select ';
6 for v_rec in (select * from t) loop
7 v_sql := v_sql || ' max(decode(name, ''' || v_rec.name || ''', id)) as "' || v_rec.name || '",';
8 end loop;
9 v_sql := rtrim(v_sql, ',');
10 v_sql := v_sql || ' from t';
11 open p for v_sql;
12 dbms_output.put_line(v_sql);
13 end;
14 /过程已创建。scott@ORA1> var x2 refcursor
scott@ORA1> exec p_test(:x2);
select ( select id from t where name='上海' and rownum = 1) as " 上海 ", ( select id from t where name='北京' and
rownum = 1) as " 北京 ", ( select id from t where name='天津' and rownum = 1) as " 天津 ", ( select id from t where
name='湖北' and rownum = 1) as " 湖北 ", ( select id from t where name='湖南' and rownum = 1) as " 湖南 " from dualPL/SQL 过程已成功完成。scott@ORA1> print x2; 上海 北京 天津 湖北 湖南
---------- ---------- ---------- ---------- ----------
101 102 103 104 105scott@ORA1> exec p_test2(:x2);
select max(decode(name, '上海', id)) as "上海", max(decode(name, '北京', id)) as "北京", max(decode(name, '天津', id))
as "天津", max(decode(name, '湖北', id)) as "湖北", max(decode(name, '湖南', id)) as "湖南" from tPL/SQL 过程已成功完成。scott@ORA1> print x2; 上海 北京 天津 湖北 湖南
---------- ---------- ---------- ---------- ----------
101 102 103 104 105