行列转换问题,网上很多的。去找找。
这里有个例子:
姓名 工资项 工资
张三 基本工资 1000
张三 岗位工资 2000
张三 效益工资 200
李四 基本工资 1000
李四 效益工资 1000
.......我需要用一个SQL语句得到如下所示的结果:
姓名 基本工资 岗位工资 效益工资 .....
张三 1000 2000 200 .....
李四 1000 0 1000 .....
select
name 姓名,
sum(basic) 基本工资,
sum(job) 岗位工资,
sum(xiaoyi) 效益工资
from
(
select
name name,
decode(a.工资项,'基本工资',a.工资,0) basic,
decode(a.工资项,'岗位工资',a.工资,0) job,
decode(a.工资项,'效益工资',a.工资,0) xiaoyi
from
tablea a
)
group by
name
要求:在你的工资项中某个人的工资项的个数该是相对固定的,有几个项就写几个decode
Excel的数据透视图能够实现。
--=======================================
declare
sql1 varchar(8000);
sql2 varchar(8000);
t number(2);
CURSOR c_test is select distinct a.工资项 from test1 a;
begin
sql1:='select 姓名 ';
sql2:='select 姓名 ';
for v_test in c_test loop
sql2 := sql2 ||', nvl(sum('||v_test.工资项||'),0)'||v_test.工资项;
sql1 := sql1 ||','|| 'case when 工资项 = '|| chr(39)||v_test.工资项||chr(39)||' then nvl(sum(工资),0) end '|| v_test.工资项;
end loop;
sql1 := sql1 || ' from test1 group by 姓名,工资项';select count(*) into t from all_all_tables a where a.table_name = 'TESTAA';
sql2 := 'create table test2 as '|| sql2||' from ('||sql1||') group by 姓名';
if t>0 then
begin
sql1:='drop table test2 ';
EXECUTE IMMEDIATE sql1;
end ;
end if;
EXECUTE IMMEDIATE sql2;
end ;然后select * from test2 看看
--===================================/*
Create table test (name varchar2(10),km varchar2(10),cj number)
go
insert into test values('张三','语文',80);
insert into test values('张三','数学',86);
insert into test values('张三','英语',75);
insert into test values('李四','语文',78);
insert into test values('李四','数学',85);
insert into test values('李四','英语',78);
select * from test;select name,
sum(decode(km,'语文',cj,0)) as 语文,
sum(decode(km,'数学',cj,0)) as 数学,
sum(decode(km,'英语',cj,0)) as 英语
from test
group by name;
*/CREATE OR REPLACE PACKAGE Pack_Test
AS
TYPE QUERY_CURSOR IS REF CURSOR;
PROCEDURE My_cur_out(
result out QUERY_CURSOR);
END Pack_Test;
CREATE OR REPLACE PACKAGE BODY Pack_Test
AS
PROCEDURE My_cur_out(
result out QUERY_CURSOR
)
AS
BEGIN
DECLARE
v_result varchar2(4000);
v_table varchar2(255):='TEST';--表名
v_col1 varchar2(255):='NAME'; --表的第一个列
v_col2 varchar2(255):='KM'; --表的第二个列
v_col3 varchar2(255):='CJ'; --表的第三个列
v_value varchar2(255);
v_file utl_file.file_type;
TYPE cur_type IS REF CURSOR;
cur_row cur_type;
BEGIN
v_result := 'SELECT '||v_col1;
OPEN cur_row FOR 'SELECT DISTINCT '||v_col2||' FROM '||v_table;
LOOP
FETCH cur_row INTO v_value;
EXIT WHEN cur_row%NOTFOUND;
v_result := v_result||',sum(decode('||v_col2||','''||v_value||''','||v_col3||','''')) as '||v_value;
END LOOP;
CLOSE cur_row;
v_result := v_result||' from '||v_table||' group by '||v_col1;
OPEN result FOR v_result;
END;
END;
END Pack_Test;至于参数,就自己加上就行了。
v_table varchar2(255):='TEST';--表名
v_col1 varchar2(255):='NAME'; --表的第一个列
v_col2 varchar2(255):='KM'; --表的第二个列
v_col3 varchar2(255):='CJ'; --表的第三个列
把这三个变量改成三个参数就行了。
整个代码全部动态游标实现的。
这里有个例子:
姓名 工资项 工资
张三 基本工资 1000
张三 岗位工资 2000
张三 效益工资 200
李四 基本工资 1000
李四 效益工资 1000
.......我需要用一个SQL语句得到如下所示的结果:
姓名 基本工资 岗位工资 效益工资 .....
张三 1000 2000 200 .....
李四 1000 0 1000 .....
select
name 姓名,
sum(basic) 基本工资,
sum(job) 岗位工资,
sum(xiaoyi) 效益工资
from
(
select
name name,
decode(a.工资项,'基本工资',a.工资,0) basic,
decode(a.工资项,'岗位工资',a.工资,0) job,
decode(a.工资项,'效益工资',a.工资,0) xiaoyi
from
tablea a
)
group by
name
要求:在你的工资项中某个人的工资项的个数该是相对固定的,有几个项就写几个decode
Excel的数据透视图能够实现。
--=======================================
declare
sql1 varchar(8000);
sql2 varchar(8000);
t number(2);
CURSOR c_test is select distinct a.工资项 from test1 a;
begin
sql1:='select 姓名 ';
sql2:='select 姓名 ';
for v_test in c_test loop
sql2 := sql2 ||', nvl(sum('||v_test.工资项||'),0)'||v_test.工资项;
sql1 := sql1 ||','|| 'case when 工资项 = '|| chr(39)||v_test.工资项||chr(39)||' then nvl(sum(工资),0) end '|| v_test.工资项;
end loop;
sql1 := sql1 || ' from test1 group by 姓名,工资项';select count(*) into t from all_all_tables a where a.table_name = 'TESTAA';
sql2 := 'create table test2 as '|| sql2||' from ('||sql1||') group by 姓名';
if t>0 then
begin
sql1:='drop table test2 ';
EXECUTE IMMEDIATE sql1;
end ;
end if;
EXECUTE IMMEDIATE sql2;
end ;然后select * from test2 看看
--===================================/*
Create table test (name varchar2(10),km varchar2(10),cj number)
go
insert into test values('张三','语文',80);
insert into test values('张三','数学',86);
insert into test values('张三','英语',75);
insert into test values('李四','语文',78);
insert into test values('李四','数学',85);
insert into test values('李四','英语',78);
select * from test;select name,
sum(decode(km,'语文',cj,0)) as 语文,
sum(decode(km,'数学',cj,0)) as 数学,
sum(decode(km,'英语',cj,0)) as 英语
from test
group by name;
*/CREATE OR REPLACE PACKAGE Pack_Test
AS
TYPE QUERY_CURSOR IS REF CURSOR;
PROCEDURE My_cur_out(
result out QUERY_CURSOR);
END Pack_Test;
CREATE OR REPLACE PACKAGE BODY Pack_Test
AS
PROCEDURE My_cur_out(
result out QUERY_CURSOR
)
AS
BEGIN
DECLARE
v_result varchar2(4000);
v_table varchar2(255):='TEST';--表名
v_col1 varchar2(255):='NAME'; --表的第一个列
v_col2 varchar2(255):='KM'; --表的第二个列
v_col3 varchar2(255):='CJ'; --表的第三个列
v_value varchar2(255);
v_file utl_file.file_type;
TYPE cur_type IS REF CURSOR;
cur_row cur_type;
BEGIN
v_result := 'SELECT '||v_col1;
OPEN cur_row FOR 'SELECT DISTINCT '||v_col2||' FROM '||v_table;
LOOP
FETCH cur_row INTO v_value;
EXIT WHEN cur_row%NOTFOUND;
v_result := v_result||',sum(decode('||v_col2||','''||v_value||''','||v_col3||','''')) as '||v_value;
END LOOP;
CLOSE cur_row;
v_result := v_result||' from '||v_table||' group by '||v_col1;
OPEN result FOR v_result;
END;
END;
END Pack_Test;至于参数,就自己加上就行了。
v_table varchar2(255):='TEST';--表名
v_col1 varchar2(255):='NAME'; --表的第一个列
v_col2 varchar2(255):='KM'; --表的第二个列
v_col3 varchar2(255):='CJ'; --表的第三个列
把这三个变量改成三个参数就行了。
整个代码全部动态游标实现的。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货