关于Oralce的行列转换
现在有个表的结构如下
clum : id name1 number1 name2 number2 name3 number3 ...... name30 number30
date : 001 n1 100 n2 200 n3 300 ....... n30 300000
现在想通过SQL处理变成
clum id name number
data 001 n1 100
001 n2 200
001 n3 300
......
001 n30 300000
请问这个应该如何处理
现在我的方法是
select id name1 number1 from table
union all
select id name2 number2 from table
union all
.........
select id name30 number30 from table但是这样的做法感觉不太合适
问问大家有没有 比较简练的解决方法？
现在有个表的结构如下
clum : id name1 number1 name2 number2 name3 number3 ...... name30 number30
date : 001 n1 100 n2 200 n3 300 ....... n30 300000
现在想通过SQL处理变成
clum id name number
data 001 n1 100
001 n2 200
001 n3 300
......
001 n30 300000
请问这个应该如何处理
现在我的方法是
select id name1 number1 from table
union all
select id name2 number2 from table
union all
.........
select id name30 number30 from table但是这样的做法感觉不太合适
问问大家有没有 比较简练的解决方法？
解决方案 »
- 在linux下安装oracle，是否要专门的用户和组
- java 中调用oracle 存储过程到execute（）就不执行了
- pl/sql中F8编译存储过程的时候,pl/sql就死掉了,每次都这样,怎么解决?
- 请教？
- 各位大虾 请教个问题！还请多多帮忙，怎么配置oracle，使实例内存用到2G ,谢谢了！分不够在加！
- 想请问一下sqlplus在没有用到监听器的时候是怎样与本地数据库连接的,,,,
- 把新年的祝福带给大家
- 请问Oracle 9i里面怎么做动态表单？
- 安装Oracle9i到100％时出错！
- 在“服务”中不能启动oracle问题
- 关于修改oracle http://localhost:5500/em 数据库登录字符串的问题
- Oracle中如何新建数据库?
PHP code:--------------------------------------------------------------------------------
create or replace
procedure print_table
( p_query in varchar2,
p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be "friendly"
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;
-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;
-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;
-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);
-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end if;
end loop;
dbms_output.put_line( '----------------- );
end loop;
-- now, restore the session state, no matter what
restore;
exception
when others then
restore;
raise;
end;
/
SQL> exec print_table('select * from emp');
EMPNO : 7369
ENAME : SMITH
JOB : CLERK
MGR : 7902
HIREDATE : 17-dec-1980 00:00:00
SAL : 800
COMM :
DEPTNO : 20
-----------------
EMPNO : 7499
ENAME : ALLEN
JOB : SALESMAN
MGR : 7698
HIREDATE : 20-feb-1981 00:00:00
SAL : 1600
COMM : 300
DEPTNO : 30
-----------------
EMPNO : 7521
ENAME : WARD
JOB : SALESMAN
MGR : 7698
HIREDATE : 22-feb-1981 00:00:00
SAL : 1250
COMM : 500
DEPTNO : 30
-----------------
EMPNO : 7566
ENAME : JONES
JOB : MANAGER
MGR : 7839
HIREDATE : 02-apr-1981 00:00:00
SAL : 2975
COMM :
DEPTNO : 20
-----------------
EMPNO : 7654
ENAME : MARTIN
JOB : SALESMAN
MGR : 7698
HIREDATE : 28-sep-1981 00:00:00
SAL : 1250
COMM : 1400
DEPTNO : 30
-----------------
EMPNO : 7698
ENAME : BLAKE
JOB : MANAGER
MGR : 7839
HIREDATE : 01-may-1981 00:00:00
SAL : 2850
COMM :
DEPTNO : 30
-----------------
EMPNO : 7782
ENAME : CLARK
JOB : MANAGER
MGR : 7839
HIREDATE : 09-jun-1981 00:00:00
SAL : 2450
COMM :
DEPTNO : 10
-----------------
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
MGR : 7566
HIREDATE : 19-apr-1987 00:00:00
SAL : 3000
COMM :
DEPTNO : 20
-----------------
EMPNO : 7839
ENAME : KING
JOB : PRESIDENT
MGR :
HIREDATE : 17-nov-1981 00:00:00
SAL : 5000
COMM :
DEPTNO : 10
-----------------
EMPNO : 7844
ENAME : TURNER
JOB : SALESMAN
MGR : 7698
HIREDATE : 08-sep-1981 00:00:00
SAL : 1500
COMM : 0
DEPTNO : 30
-----------------
EMPNO : 7876
ENAME : ADAMS
JOB : CLERK
MGR : 7788
HIREDATE : 23-may-1987 00:00:00
SAL : 1100
COMM :
DEPTNO : 20
-----------------
EMPNO : 7900
ENAME : JAMES
JOB : CLERK
MGR : 7698
HIREDATE : 03-dec-1981 00:00:00
SAL : 950
COMM :
DEPTNO : 30
-----------------
EMPNO : 7902
ENAME : FORD
JOB : ANALYST
MGR : 7566
HIREDATE : 03-dec-1981 00:00:00
SAL : 3000
COMM :
DEPTNO : 20
-----------------
EMPNO : 7934
ENAME : MILLER
JOB : CLERK
MGR : 7782
HIREDATE : 23-jan-1982 00:00:00
SAL : 1300
COMM :
DEPTNO : 10
-----------------
PL/SQL procedure successfully completed.
SQL>
create or replace
procedure print_table
( p_query in varchar2,
p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate 'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format='''||l_date_fmt||'''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format='''||p_date_fmt||'''';
end if;
-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;
-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;
-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);
-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
||': '||
substr( l_columnValue, 1, 200 ) );
end if;
end loop;
dbms_output.put_line( '-----------------' );
end loop;
-- now, restore the session state, no matter what
restore;
exception
when others then
restore;
raise;
end;
/