背景:公司产品的测试数据在oracle数据库中,后台表的列名为:产品SN、测试项、测试值等。
如下图:
这样存储的好处是,不限定测试项目名称,且支持用户在前端随意增删测试项。
但是,用户想在展示报表的时候,将测试项目打横,展示成如下图这样:
本着上兵伐谋的原则,一开始让用户自己使用excel的vlookup函数自己处理,但是用户也清楚由于测试项目不是固定的,很难用excel做出一个稳定的模板。
于是还是要自己进行开发,话不多说,无非就是使用oracle数据库中自带的PIVOT函数,结合一下存储过程,写一段动态sql:
oracle 存储过程代码开始:
---------------------------------------------------------------------------------------------------------------------------------
create or replace procedure P_EXPORTDATA_RTOC
/*
测试数据导出(行转列)
*/
(
M_SN IN VARCHAR2,
M_MO IN VARCHAR2,
M_RES OUT VARCHAR2,
CUR_SQL OUT SYS_REFCURSOR
)
AS
L_COUNT NUMBER;
l_str VARCHAR2(5000);
l_SQL varchar2(10000);
begin
M_RES:='OK';
FOR I IN (SELECT distinct d.tpd_name
FROM scstadmin.t_Wip_Tracking a
join scstadmin.T_TEST_RESULT b
on a.serial_number = b.ts_product_sn
JOIN SCSTADMIN.T_TEST_PRODUCT_INFOR c
ON b.TS_ID = c.TS_ID
JOIN SCSTADMIN.T_TEST_PRODUCT_DATA d
ON c.TPI_ID = d.TPI_ID
where b.ts_result = 'T' and a.mo_number = M_MO
and a.serial_number in (SELECT * FROM TABLE(SCSTADMIN.SPLIT(M_SN)))
)
LOOP
l_str := l_str || CHR(39) || I.tpd_name || CHR(39) || ' as ' ||
REPLACE(trim(I.tpd_name), ' ', '_') || ',';
END LOOP;
SELECT SUBSTR(l_str, 1, LENGTH(l_str) - 1)
INTO L_STR
FROM DUAL;
l_SQL := '
select *
from (SELECT A.SERIAL_NUMBER AS SN,A.MO_NUMBER,D.tpd_name,D.tpd_value
FROM scstadmin.t_Wip_Tracking a
join scstadmin.T_TEST_RESULT b
on a.serial_number = b.ts_product_sn
JOIN SCSTADMIN.T_TEST_PRODUCT_INFOR c
ON b.TS_ID = c.TS_ID
JOIN SCSTADMIN.T_TEST_PRODUCT_DATA d
ON c.TPI_ID = d.TPI_ID
where b.ts_result = ''T'' and a.mo_number = '''||M_MO||'''
and a.serial_number in (SELECT * FROM TABLE(SCSTADMIN.SPLIT('''||M_SN||''')))
) PIVOT(max(tpd_value) FOR tpd_name IN (' || l_str ||'))';
DELETE FROM SCSTADMIN.t_report_sql_log T WHERE T.SQL_WHERE = M_SN;
COMMIT;
insert into scstadmin.t_report_sql_log
(id, SQL_STR, FUNC_NAME, SQL_WHERE)
values
(scstadmin.SEQ_t_report_sql_log.nextval, l_SQL, 'P_EXPORTDATA_RTOC', M_SN);
commit;
OPEN CUR_SQL FOR l_SQL;
exception
when others then
m_res:='NG:'||sqlcode||sqlerrm;
end;
---------------------------------------------------------------------------------------------------------------------------------oracle 存储过程代码结束
写完之后,运行存储过程,得到结果集游标如下图:
对比excel,是用户想要的功能,完成。