原表格式与格式是这样的一个格式
A1 A2 A3
指标1 11 1
指标2 12 1
指标3 13 1
指标1 14 2
指标2 15 2
指标3 16 2
指标1 17 3最终想要的一个结果为
指标1 指标2 指标3
11 12 13
14 15 16
17 0 0 现在在SQLServer中已经写好了这个语句DECLARE @sql VARCHAR(8000)
SET @sql = 'select A3'
SELECT @sql = @sql + ' ,max(case A1 when ''' + A1
+ ''' then A2 else 0 end) [' + A1 + ']'
FROM ( SELECT DISTINCT
A1
FROM Table_1
) AS a
SET @sql = @sql + ' from Table_1 group by A3'
EXEC(@sql)
现在要把他转换成Oracle。但是由于对Oracle不太熟悉。所以请大家帮帮忙
A1 A2 A3
指标1 11 1
指标2 12 1
指标3 13 1
指标1 14 2
指标2 15 2
指标3 16 2
指标1 17 3最终想要的一个结果为
指标1 指标2 指标3
11 12 13
14 15 16
17 0 0 现在在SQLServer中已经写好了这个语句DECLARE @sql VARCHAR(8000)
SET @sql = 'select A3'
SELECT @sql = @sql + ' ,max(case A1 when ''' + A1
+ ''' then A2 else 0 end) [' + A1 + ']'
FROM ( SELECT DISTINCT
A1
FROM Table_1
) AS a
SET @sql = @sql + ' from Table_1 group by A3'
EXEC(@sql)
现在要把他转换成Oracle。但是由于对Oracle不太熟悉。所以请大家帮帮忙
(
SELECT '指标1' a, 11 b, 1 c FROM dual
UNION ALL
SELECT '指标2', 12, 1 FROM dual
UNION ALL
SELECT '指标3', 13, 1 FROM dual
UNION ALL
SELECT '指标1', 14, 2 FROM dual
UNION ALL
SELECT '指标2', 15, 2 FROM dual
UNION ALL
SELECT '指标3', 16, 2 FROM dual
UNION ALL
SELECT '指标1', 17, 3 FROM dual
)SELECT MAX(CASE WHEN a='指标1' THEN b ELSE 0 END) 指标1,
MAX(CASE WHEN a='指标2' THEN b ELSE 0 END) 指标2,
MAX(CASE WHEN a='指标3' THEN b ELSE 0 END) 指标3
FROM t GROUP BY c --result:
11 12 13
14 15 16
17 0 0
--另一种写法!WITH t AS
(
SELECT '指标1' a, 11 b, 1 c FROM dual
UNION ALL
SELECT '指标2', 12, 1 FROM dual
UNION ALL
SELECT '指标3', 13, 1 FROM dual
UNION ALL
SELECT '指标1', 14, 2 FROM dual
UNION ALL
SELECT '指标2', 15, 2 FROM dual
UNION ALL
SELECT '指标3', 16, 2 FROM dual
UNION ALL
SELECT '指标1', 17, 3 FROM dual
)
SELECT nvl(substr(a_str,1,instr(a_str,',',1,1)-1),0) 指标1,
CASE WHEN instr(a_str,',',1,2)>0 THEN substr(a_str,instr(a_str,',',1,1)+1,instr(a_str,',',1,2)-instr(a_str,',',1,1)-1) ELSE '0' END 指标2,
CASE WHEN instr(a_str,',',1,2)>0 THEN substr(a_str,instr(a_str,',',1,2)+1,length(a_str)-instr(a_str,',',1,2)-1) ELSE '0' END 指标3
FROM
(
SELECT MAX(substr(sys_connect_by_path(b,','),2))||',' a_str
FROM
(
SELECT t.*,row_number()over(PARTITION BY c ORDER BY a) rn FROM t
)
START WITH rn=1
CONNECT BY rn-1=PRIOR rn AND c=PRIOR c
GROUP BY c
)--result:
11 12 13
14 15 16
17 0 0
max(case A1 when '指标1' then A2 else 0 end) 指标1,
max(case A1 when '指标1' then A2 else 0 end) 指标2,
max(case A1 when '指标1' then A2 else 0 end) 指标3
from tb
group by A3
max(case A1 when '指标1' then A2 else 0 end) 指标1,
max(case A1 when '指标1' then A2 else 0 end) 指标2,
max(case A1 when '指标1' then A2 else 0 end) 指标3
from tb
group by A3select
max(decode(A1 , '指标1' , a2 , 0)) 指标1,
max(decode(A1 , '指标2' , a2 , 0)) 指标2,
max(decode(A1 , '指标3' , a2 , 0)) 指标3
from tb
group by a3
var_sql_str varchar2(2000);
type test_array is table of Table_1.A1%type INDEX BY BINARY_INTEGER;
var_test_array test_array;
begin
select distinct A1 bulk collect into var_test_array from Table_1;
var_sql_str:='select A3'
for i in 1..var_test_array.count loop
begin
var_sql_str:=var_sql_str||', max(case A1 when '''||var_test_array(i)||''' then A2 else 0 end) as '||var_test_array(i)||''
end loop;
var_sql_str:=var_sql_str||' from Table_1 group by A3'
execute immediate var_sql_str;
end ;
declare
var_sql_str varchar2(2000);
type test_array is table of Table_1.A1%type INDEX BY BINARY_INTEGER;
var_test_array test_array;
begin
select distinct A1 bulk collect into var_test_array from Table_1;
var_sql_str:='select A3'
for i in 1..var_test_array.count loop
begin
var_sql_str:=var_sql_str||', max(case A1 when '''||var_test_array(i)||''' then A2 else 0 end) as '||var_test_array(i)||'';
end loop;
var_sql_str:=var_sql_str||' from Table_1 group by A3';
execute immediate var_sql_str;
end ;