--> Test Data: [T]
if object_id('[T]') is not null drop table [T]
create table [T] (ID int identity(1,1),[GJSJ] int,[JKYL] numeric(2,1))
insert into [T]
select 0,5.0 union all
select 4,5.2 union all
select 12,4.8 union all
select 20,4.6 union all
select 28,4.5 select * from [T]
--Code
declare @s varchar(8000),@s2 varchar(8000)
select @s=ISNULL(@S+',','')+'max(case when [GJSJ]='''+ltrim([GJSJ])+''' then [GJSJ] end) as ''colum'+ltrim([id]+1)+'''',
@s2=ISNULL(@S2+',','')+''+ltrim([JKYL])+'' from T
--print @S
--print @s2
exec('select ''GJSJ'' as colum1,'+@S+' from [T] union all select ''JKYL'','+@s2+'')
--Drop
drop table [T]
--Result
/*
colum1 colum2 colum3 colum4 colum5 colum6
------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
GJSJ 0.0 4.0 12.0 20.0 28.0
JKYL 5.0 5.2 4.8 4.6 4.5
*/
if object_id('[T]') is not null drop table [T]
create table [T] (ID int identity(1,1),[GJSJ] int,[JKYL] numeric(2,1))
insert into [T]
select 0,5.0 union all
select 4,5.2 union all
select 12,4.8 union all
select 20,4.6 union all
select 28,4.5 select * from [T]
--Code
declare @s varchar(8000),@s2 varchar(8000)
select @s=ISNULL(@S+',','')+'max(case when [GJSJ]='''+ltrim([GJSJ])+''' then [GJSJ] end) as ''colum'+ltrim([id]+1)+'''',
@s2=ISNULL(@S2+',','')+''+ltrim([JKYL])+'' from T
--print @S
--print @s2
exec('select ''GJSJ'' as colum1,'+@S+' from [T] union all select ''JKYL'','+@s2+'')
--Drop
drop table [T]
--Result
/*
colum1 colum2 colum3 colum4 colum5 colum6
------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
GJSJ 0.0 4.0 12.0 20.0 28.0
JKYL 5.0 5.2 4.8 4.6 4.5
*/
解决方案 »
- oracle 10g 升级到oracle 11g sql报错,跪求大神解答
- partition table 删除 partition
- 关于赋权限的问题
- 创建任务报错,如何搞?
- 用spool,如何在存储过程执行过程中输出信息?
- 如何查看非本地库
- 有关TCP协议中的端口号和其他端口的区别?解析
- 如何用SQL查看Table的Comment?
- 请问配置分布式数据库需要oracle的哪些套件呢??
- oracle用oder by 排序emp表后,emp表和原来的表一样吗?
- 使用oracle数据库的过程中发现日志过大
- linux下shell编程菜鸟级问题:关于定时逻辑备份Oracle 10g的shell脚本,然后想用cron定时执行
select 0,5.0 from dual union all
select 4,5.2 from dual union all
select 12,4.8 from dual union all
select 20,4.6 from dual union all
select 28,4.5 from dualoracle 没@s这样的用法,直接变量名
变量赋值
s2:=
字符串连接不能用+,要用shift+'\'那个
'字符串1'||'字符串2'
insert into TableA select 0,5.0 from dual;
insert into TableA select 4,5.2 from dual ;
insert into TableA select 12,4.8 from dual ;
insert into TableA select 20,4.6 from dual ;
insert into TableA select 28,4.5 from dual ;这个插入代码在Oracle里面可以用了。后半部分正在研究中
@str1 = isnull(@str1, '') + ',''' + cast( gjsj as varchar ) + ''' as [column'+ cast(@t as varchar) +']',
@str2 = isnull(@str2, '') + ',''' + cast( jkyl as varchar ) + ''' as [column'+ cast(@t as varchar) +']'
from @tbset @str1 = 'select ''GJSJ'' as [column1]' + @str1
set @str2 = 'select ''KJYL'' as [column1]' + @str2exec( @str1 + ' union all ' + @str2 )--结果
/*
column1 column2 column3 column4 column5 column6
------- ------- ------- ------- ------- -------
GJSJ 0 4 12 20 28
KJYL 5.0 5.2 4.8 4.6 4.5*/各位Oracle达人,上面的代码如何转换在Oracle中运行?请各位告知,谢谢!
declare v_str1 varchar2(4000);
v_str2 varchar2(4000);
v_t int;
begin
select nvl(v_t,1) + 1 into v_t,
nvl(v_str1, '') || ',''' || to_char(gjsj) || ''' as [column' || to_char(v_t) || ']' into v_str1,
nvl(v_str2, '') || ',''' || to_char(jkyl) || ''' as [column' || to_char(v_t) || ']' into v_str2
from @tb
execute( v_str1 || ' union all ' || v_str2 )
end;
If you want to get data result, you need to run another procedure.
like :
get_data(v_str1 || ' union all ' || v_str2 )
get_data is a procedure you defined to get data
v_str1 varchar2(4000),
v_str2 varchar2(4000),
v_t int
)
as
begin
select nvl(v_t,1) + 1 into v_t,
nvl(v_str1, '') || ',''' || to_char(gjsj) || ''' as [column' || to_char(v_t) || ']' into v_str1,
nvl(v_str2, '') || ',''' || to_char(jkyl) || ''' as [column' || to_char(v_t) || ']' into v_str2
from TABLEA
execute( v_str1 || ' union all ' || v_str2 )
commit;
exception
when others then
rollback;
end PYJSJ;这个存储过程编译通不过啊,怎么办呢?
v_t number;
v_str1 varchar2(4000);
v_str2 varchar2(4000);
cursor c1 is
select nvl(v_t, 1) + 1,
nvl(v_str1, '') || ',' || to_char(gjsj) || ' as [column ' ||
to_char(v_t) || ']',
nvl(v_str2, '') || ',' || to_char(jkyl) || ' as [column' ||
to_char(v_t) || ']'
from tableA;
begin
open c1;
fetch c1
into v_t, v_str1, v_str2;
close c1;
end;