You Asked
I have two columns with Column_2 containing string of various codes separated by commas for each person. Some person may have more or less codes. The codes are in different order from person to person. Below are just three examples of the 6000K+ records.
Column_1 Column_2
PersonA: E103,O205,M345,CN046,O1030
PersonB: O076,E020,V3000,E103
PersonC: M1250,E2000,F1034,O1030,E094,E005,O205,TF094,E003
I want to write a query from Table_1 that have the following conditions:
1. For each person, extract codes having letter "E" and "O" only, then place each code in a new column. For example for PersonA, extract codes E103,O025,O1030 then place each code into new Column_3,Column_4,Column_5 respectively.
2. Link each code to Table_B to get code description, then concatenate them. This can be done in conjunction with condition 1 above.
3. Group all codes/description that are alike then Count them.
In summary, I want to generate a report that has group of all the E and O codes and their count. Something that looks like this:
Code/Decription Count
E103-Error1 23
O025-Overage1 108
O1030-Overage2 909
and so on...
Thanks for your help.
and Connor said...
Can you explain steps "2" and "3" with some more examples please.
And is there any particular reason you want the values in *columns* ? That seems odd because you end up with an arbitrary number of columns in the table. That also makes it harder to process, eg, Person A will have E103 in column 1, but Person will have E103 in column 4.
Here is an example using pipeline functions. Note - how you choose to parse the string is your choice (I used the select from dual etc) but you could just as easily walk along the string calling pipe as you go.
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t ( p int, s varchar2(200));
Table created.
SQL>
SQL> insert into t values (1,'E103,O205,M345,CN046,O1030');
1 row created.
SQL> insert into t values (2,'O076,E020,V3000,E103');
1 row created.
SQL> insert into t values (3,'M1250,E2000,F1034,O1030,E094,E005,O205,TF094,E003');
1 row created.
SQL>
SQL>
SQL> create or replace type vc_list is table of varchar2(20)
2 /
Type created.
SQL>
SQL> create or replace
2 function F return vc_list pipelined is
3 begin
4 for i in ( select * from T ) loop
5
6 for j in (
7 select substr(i.s,
8 loc+1,nvl(
9 lead(loc) over ( order by loc ) - loc-1,
10 length(i.s)-loc)
11 ) r
12 from (
13 select distinct (instr(i.s,',',1,level)) loc
14 from dual
15 connect by level < length(i.s)-length(replace(i.s,','))+1
16 )
17 )
18 loop
19 pipe row ( j.r );
20 end loop;
21 end loop;
22 return;
23 end;
24 /
Function created.
SQL>
SQL> select * from table(f);
COLUMN_VALUE
--------------------
O205
M345
CN046
O1030
E020
V3000
E103
E2000
F1034
O1030
E094
E005
O205
TF094
E003
15 rows selected.
SQL>
SQL> select column_value, count(*)
2 from table(f)
3 group by column_value;
COLUMN_VALUE COUNT(*)
-------------------- ----------
CN046 1
E020 1
E005 1
E2000 1
O205 2
TF094 1
O1030 2
V3000 1
E103 1
M345 1
E094 1
E003 1
F1034 1
13 rows selected.
SQL>
SQL>
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment