主要ORACLE 函数 wm_concat 的应用: 先看TIPTOP 界面的效果图(如下)
举例:建立测试table,insert数据,查看效果
1.建测试表
===================================================
create table temp_test(
sfb82 varchar(20),
gem02 varchar(20),
shift varchar(20),
eci06 varchar(20),
tc_sfb022 date,
tc_sfb01 varchar(16),
l_days number(5,0)
)
===================================================
2.INSERT 几笔数据进去
==============================================================================================
insert into temp_test values ('D00301','装配A课', 'A:白班','印刷1#机', to_date('20111125','yyyy/mm/dd'),'511-11110193');
insert into temp_test values ('D00301','装配A课', 'A:白班','印刷1#机', to_date('20111125','yyyy/mm/dd'),'511-11110200');
insert into temp_test values ('D00301','装配A课', 'A:白班','印刷1#机', to_date('20111125','yyyy/mm/dd'),'511-11110207');
insert into temp_test values ('D00301','装配A课', 'A:白班','印刷2#机', to_date('20111130','yyyy/mm/dd'),'511-11110223');
insert into temp_test values ('D00301','装配A课', 'A:白班','印刷3#机', to_date('20111124','yyyy/mm/dd'),'511-11110189');
insert into temp_test values ('D00301','装配A课', 'A:白班','印刷3#机', to_date('20111130','yyyy/mm/dd'),'511-11110219');
insert into temp_test values ('D00301','装配A课', 'A:白班','组装3#线', to_date('20111130','yyyy/mm/dd'),'511-11110418');
insert into temp_test values ('D00301','装配A课', 'B:夜班','印刷1#机', to_date('20111125','yyyy/mm/dd'),'511-11110193');
insert into temp_test values ('D00301','装配A课', 'B:夜班','印刷3#机', to_date('20111124','yyyy/mm/dd'),'511-11110189');
insert into temp_test values ('D00102','机台二课','A:白班','机台F区20#',to_date('20111124','yyyy/mm/dd'),'511-11110332');
insert into temp_test values ('D00102','机台二课','B:夜班','机台F区20#',to_date('20111124','yyyy/mm/dd'),'511-11110332');
insert into temp_test values ('D00103','机台三课','A:白班','机台H区11#',to_date('20111130','yyyy/mm/dd'),'511-11110403');
insert into temp_test values ('D00103','机台三课','B:夜班','机台H区11#',to_date('20111130','yyyy/mm/dd'),'511-11110403');
insert into temp_test values ('D00105','射吹课' , 'A:白班','射吹课1#', to_date('20111115','yyyy/mm/dd'),'511-11110041');
insert into temp_test values ('D00105','射吹课' , 'B:夜班','射吹课1#', to_date('20111115','yyyy/mm/dd'),'511-11110041');
insert into temp_test values ('D00201','喷涂A课', 'A:白班','UV盘线1#机',to_date('20111117','yyyy/mm/dd'),'511-11110430');
insert into temp_test values ('D00201','喷涂A课', 'A:白班','UV盘线1#机',to_date('20111124','yyyy/mm/dd'),'511-11110432');
insert into temp_test values ('D00201','喷涂A课', 'B:夜班','UV盘线1#机',to_date('20111117','yyyy/mm/dd'),'511-11110430');
insert into temp_test values ('D00201','喷涂A课', 'B:夜班','UV盘线1#机',to_date('20111124','yyyy/mm/dd'),'511-11110432');
==============================================================================================
3.小计与合计(横向与纵向)
==============================================================================================
select sfb82,gem02,shift,eci06,count(tc_sfb01),
wm_concat (case to_char(tc_sfb022,'dd') when '01' then tc_sfb01 ELSE null end) d01,
wm_concat (case to_char(tc_sfb022,'dd') when '02' then tc_sfb01 ELSE null end) d02,
wm_concat (case to_char(tc_sfb022,'dd') when '03' then tc_sfb01 ELSE null end) d03,
wm_concat (case to_char(tc_sfb022,'dd') when '04' then tc_sfb01 ELSE null end) d04,
wm_concat (case to_char(tc_sfb022,'dd') when '05' then tc_sfb01 ELSE null end) d05,
wm_concat (case to_char(tc_sfb022,'dd') when '06' then tc_sfb01 ELSE null end) d06,
wm_concat (case to_char(tc_sfb022,'dd') when '07' then tc_sfb01 ELSE null end) d07,
wm_concat (case to_char(tc_sfb022,'dd') when '08' then tc_sfb01 ELSE null end) d08,
wm_concat (case to_char(tc_sfb022,'dd') when '09' then tc_sfb01 ELSE null end) d09,
wm_concat (case to_char(tc_sfb022,'dd') when '10' then tc_sfb01 ELSE null end) d10,
wm_concat (case to_char(tc_sfb022,'dd') when '11' then tc_sfb01 ELSE null end) d11,
wm_concat (case to_char(tc_sfb022,'dd') when '12' then tc_sfb01 ELSE null end) d12,
wm_concat (case to_char(tc_sfb022,'dd') when '13' then tc_sfb01 ELSE null end) d13,
wm_concat (case to_char(tc_sfb022,'dd') when '14' then tc_sfb01 ELSE null end) d14,
wm_concat (case to_char(tc_sfb022,'dd') when '15' then tc_sfb01 ELSE null end) d15,
wm_concat (case to_char(tc_sfb022,'dd') when '16' then tc_sfb01 ELSE null end) d16,
wm_concat (case to_char(tc_sfb022,'dd') when '17' then tc_sfb01 ELSE null end) d17,
wm_concat (case to_char(tc_sfb022,'dd') when '18' then tc_sfb01 ELSE null end) d18,
wm_concat (case to_char(tc_sfb022,'dd') when '19' then tc_sfb01 ELSE null end) d19,
wm_concat (case to_char(tc_sfb022,'dd') when '20' then tc_sfb01 ELSE null end) d20,
wm_concat (case to_char(tc_sfb022,'dd') when '21' then tc_sfb01 ELSE null end) d21,
wm_concat (case to_char(tc_sfb022,'dd') when '22' then tc_sfb01 ELSE null end) d22,
wm_concat (case to_char(tc_sfb022,'dd') when '23' then tc_sfb01 ELSE null end) d23,
wm_concat (case to_char(tc_sfb022,'dd') when '24' then tc_sfb01 ELSE null end) d24,
wm_concat (case to_char(tc_sfb022,'dd') when '25' then tc_sfb01 ELSE null end) d25,
wm_concat (case to_char(tc_sfb022,'dd') when '26' then tc_sfb01 ELSE null end) d26,
wm_concat (case to_char(tc_sfb022,'dd') when '27' then tc_sfb01 ELSE null end) d27,
wm_concat (case to_char(tc_sfb022,'dd') when '28' then tc_sfb01 ELSE null end) d28,
wm_concat (case to_char(tc_sfb022,'dd') when '29' then tc_sfb01 ELSE null end) d29,
wm_concat (case to_char(tc_sfb022,'dd') when '30' then tc_sfb01 ELSE null end) d30,
wm_concat (case to_char(tc_sfb022,'dd') when '31' then tc_sfb01 ELSE null end) d31
from temp_test
where year(tc_sfb022)='2011' and month(tc_sfb022)='11'
group by sfb82,gem02,shift,eci06
UNION
select sfb82,gem02,'','小计:',count(tc_sfb01),
to_char(count (case to_char(tc_sfb022,'dd') when '01' then tc_sfb01 ELSE null end)) d01,
to_char(count (case to_char(tc_sfb022,'dd') when '02' then tc_sfb01 ELSE null end)) d02,
to_char(count (case to_char(tc_sfb022,'dd') when '03' then tc_sfb01 ELSE null end)) d03,
to_char(count (case to_char(tc_sfb022,'dd') when '04' then tc_sfb01 ELSE null end)) d04,
to_char(count (case to_char(tc_sfb022,'dd') when '05' then tc_sfb01 ELSE null end)) d05,
to_char(count (case to_char(tc_sfb022,'dd') when '06' then tc_sfb01 ELSE null end)) d06,
to_char(count (case to_char(tc_sfb022,'dd') when '07' then tc_sfb01 ELSE null end)) d07,
to_char(count (case to_char(tc_sfb022,'dd') when '08' then tc_sfb01 ELSE null end)) d08,
to_char(count (case to_char(tc_sfb022,'dd') when '09' then tc_sfb01 ELSE null end)) d09,
to_char(count (case to_char(tc_sfb022,'dd') when '10' then tc_sfb01 ELSE null end)) d10,
to_char(count (case to_char(tc_sfb022,'dd') when '11' then tc_sfb01 ELSE null end)) d11,
to_char(count (case to_char(tc_sfb022,'dd') when '12' then tc_sfb01 ELSE null end)) d12,
to_char(count (case to_char(tc_sfb022,'dd') when '13' then tc_sfb01 ELSE null end)) d13,
to_char(count (case to_char(tc_sfb022,'dd') when '14' then tc_sfb01 ELSE null end)) d14,
to_char(count (case to_char(tc_sfb022,'dd') when '15' then tc_sfb01 ELSE null end)) d15,
to_char(count (case to_char(tc_sfb022,'dd') when '16' then tc_sfb01 ELSE null end)) d16,
to_char(count (case to_char(tc_sfb022,'dd') when '17' then tc_sfb01 ELSE null end)) d17,
to_char(count (case to_char(tc_sfb022,'dd') when '18' then tc_sfb01 ELSE null end)) d18,
to_char(count (case to_char(tc_sfb022,'dd') when '19' then tc_sfb01 ELSE null end)) d19,
to_char(count (case to_char(tc_sfb022,'dd') when '20' then tc_sfb01 ELSE null end)) d20,
to_char(count (case to_char(tc_sfb022,'dd') when '21' then tc_sfb01 ELSE null end)) d21,
to_char(count (case to_char(tc_sfb022,'dd') when '22' then tc_sfb01 ELSE null end)) d22,
to_char(count (case to_char(tc_sfb022,'dd') when '23' then tc_sfb01 ELSE null end)) d23,
to_char(count (case to_char(tc_sfb022,'dd') when '24' then tc_sfb01 ELSE null end)) d24,
to_char(count (case to_char(tc_sfb022,'dd') when '25' then tc_sfb01 ELSE null end)) d25,
to_char(count (case to_char(tc_sfb022,'dd') when '26' then tc_sfb01 ELSE null end)) d26,
to_char(count (case to_char(tc_sfb022,'dd') when '27' then tc_sfb01 ELSE null end)) d27,
to_char(count (case to_char(tc_sfb022,'dd') when '28' then tc_sfb01 ELSE null end)) d28,
to_char(count (case to_char(tc_sfb022,'dd') when '29' then tc_sfb01 ELSE null end)) d29,
to_char(count (case to_char(tc_sfb022,'dd') when '30' then tc_sfb01 ELSE null end)) d30,
to_char(count (case to_char(tc_sfb022,'dd') when '31' then tc_sfb01 ELSE null end)) d31
from temp_test
where year(tc_sfb022)='2011' and month(tc_sfb022)='11'
group by sfb82,gem02
union
select '','','','总计:',count(tc_sfb01),
to_char(count (case to_char(tc_sfb022,'dd') when '01' then tc_sfb01 ELSE null end)) d01,
to_char(count (case to_char(tc_sfb022,'dd') when '02' then tc_sfb01 ELSE null end)) d02,
to_char(count (case to_char(tc_sfb022,'dd') when '03' then tc_sfb01 ELSE null end)) d03,
to_char(count (case to_char(tc_sfb022,'dd') when '04' then tc_sfb01 ELSE null end)) d04,
to_char(count (case to_char(tc_sfb022,'dd') when '05' then tc_sfb01 ELSE null end)) d05,
to_char(count (case to_char(tc_sfb022,'dd') when '06' then tc_sfb01 ELSE null end)) d06,
to_char(count (case to_char(tc_sfb022,'dd') when '07' then tc_sfb01 ELSE null end)) d07,
to_char(count (case to_char(tc_sfb022,'dd') when '08' then tc_sfb01 ELSE null end)) d08,
to_char(count (case to_char(tc_sfb022,'dd') when '09' then tc_sfb01 ELSE null end)) d09,
to_char(count (case to_char(tc_sfb022,'dd') when '10' then tc_sfb01 ELSE null end)) d10,
to_char(count (case to_char(tc_sfb022,'dd') when '11' then tc_sfb01 ELSE null end)) d11,
to_char(count (case to_char(tc_sfb022,'dd') when '12' then tc_sfb01 ELSE null end)) d12,
to_char(count (case to_char(tc_sfb022,'dd') when '13' then tc_sfb01 ELSE null end)) d13,
to_char(count (case to_char(tc_sfb022,'dd') when '14' then tc_sfb01 ELSE null end)) d14,
to_char(count (case to_char(tc_sfb022,'dd') when '15' then tc_sfb01 ELSE null end)) d15,
to_char(count (case to_char(tc_sfb022,'dd') when '16' then tc_sfb01 ELSE null end)) d16,
to_char(count (case to_char(tc_sfb022,'dd') when '17' then tc_sfb01 ELSE null end)) d17,
to_char(count (case to_char(tc_sfb022,'dd') when '18' then tc_sfb01 ELSE null end)) d18,
to_char(count (case to_char(tc_sfb022,'dd') when '19' then tc_sfb01 ELSE null end)) d19,
to_char(count (case to_char(tc_sfb022,'dd') when '20' then tc_sfb01 ELSE null end)) d20,
to_char(count (case to_char(tc_sfb022,'dd') when '21' then tc_sfb01 ELSE null end)) d21,
to_char(count (case to_char(tc_sfb022,'dd') when '22' then tc_sfb01 ELSE null end)) d22,
to_char(count (case to_char(tc_sfb022,'dd') when '23' then tc_sfb01 ELSE null end)) d23,
to_char(count (case to_char(tc_sfb022,'dd') when '24' then tc_sfb01 ELSE null end)) d24,
to_char(count (case to_char(tc_sfb022,'dd') when '25' then tc_sfb01 ELSE null end)) d25,
to_char(count (case to_char(tc_sfb022,'dd') when '26' then tc_sfb01 ELSE null end)) d26,
to_char(count (case to_char(tc_sfb022,'dd') when '27' then tc_sfb01 ELSE null end)) d27,
to_char(count (case to_char(tc_sfb022,'dd') when '28' then tc_sfb01 ELSE null end)) d28,
to_char(count (case to_char(tc_sfb022,'dd') when '29' then tc_sfb01 ELSE null end)) d29,
to_char(count (case to_char(tc_sfb022,'dd') when '30' then tc_sfb01 ELSE null end)) d30,
to_char(count (case to_char(tc_sfb022,'dd') when '31' then tc_sfb01 ELSE null end)) d31
from temp_test
where year(tc_sfb022)='2011' and month(tc_sfb022)='11'
order by 1
==========================================================================================
4.run SQL 看到效果如下:
哈哈,wm_concat 函数还是挺有用的吧!有兴趣的可以试一下!~~~~~~