Home » SQL & PL/SQL » SQL & PL/SQL » group by common string (oracle 11g)
| group by common string [message #684001] |
Wed, 17 March 2021 18:13  |
 |
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
How can I write the sql or plsql to get the output of the below input dataset
create table toast
(key1 varchar2(10),
valuestring1 varchar2(100),
measure1 number);
SET DEFINE OFF;
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1)
Values
('6', '1,2,5,rom,tom,jom', 100);
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1)
Values
('1', '101,abc,rom', 100);
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1)
Values
('2', '201, rom,lev', 209);
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1)
Values
('3', 'rom', 400);
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1)
Values
('4', '101,jes,rom,101,3456,xdr', 100);
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1)
Values
('5', '333,111,tom', 100);
COMMIT;
Output
Key | valuestring | total
=========================================
6 | ‘rom’ | 900
5 | '333,111,tom' | 100
|
|
|
|
|
|
|
|
|
|
|
|
| Re: group by common string [message #684013 is a reply to message #684012] |
Fri, 19 March 2021 10:41  |
flyboy
Messages: 1901
Registered: November 2006
|
Senior Member |
|
|
yashi7777 wrote on Thu, 18 March 2021 15:44Sure. but is there a way to group the data on a common string value within a list of strings. Like on 'rom' in my example.
Yes, just construct an expression returning the common string value, when it is present in the column.
Otherwise return the whole column. For 'rom', it would be:
select case when instr(','||valuestring1||',', 'rom') > 0 then 'rom'
else valuestring1
end common_valuestring, sum(measure1) measure1
from toast
group by case when instr(','||valuestring1||',', 'rom') > 0 then 'rom'
else valuestring1
end;
COMMON_VALUESTRING MEASURE1
------------------------------------------------------------ ----------
333,111,tom 100
rom 909
Without that expression, e.g. VALUESTRING1 containing 'promo' would be also classified under a common string 'rom'.
(this assumes comma is used only as a separator, any value cannot contain comma)
|
|
|
|
| Re: group by common string [message #684014 is a reply to message #684012] |
Fri, 19 March 2021 10:47 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
with t1 as (
-- cleanup
-- get rid of whitespaces surrounding tokens
select key1,
trim(regexp_replace(valuestring1,'\s*,\s*',',')) valuestring1,
measure1
from toast
),
t2 as (
select token,
count(*) cnt,
max(key1) key1
from t1,
lateral(
select regexp_substr(valuestring1,'[^,]+',1,level) token
from dual
connect by level <= regexp_count(valuestring1,',') + 1
)
group by token
order by cnt desc
fetch first 1 row only
),
t3 as (
select case instr(',' || t1.valuestring1 || ',',',' || t2.token || ',')
when 0 then t1.key1
else t2.key1
end key,
case instr(',' || t1.valuestring1 || ',',',' || t2.token || ',')
when 0 then t1.valuestring1
else t2.token
end valuestring,
t1.measure1 measure
from t1,
t2
)
select key,
valuestring,
sum(measure) total
from t3
group by key,
valuestring
/
KEY VALUESTRING TOTAL
---------- ------------------------- ----------
5 333,111,tom 100
6 rom 909
SQL>
|
|
|
|
|
|
| Re: group by common string [message #684054 is a reply to message #684014] |
Mon, 29 March 2021 20:49 |
|
yashi7777
Messages: 42
Registered: March 2016
|
Member |
|
|
My requirement needs the below variation on top of the excellent solution you provided; it has another key within it and we need to do the calculation based on that and also if there is a common key we need to add that, can you help on that. I tried just couldn't figure anything out, i am sorry and clueless.
SET DEFINE OFF;
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('6', '1,2,5,rom,tom,jom', 100, 'y');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('1', '101,abc,rom', 100, 'x');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('2', '201,rom,lev', 200, 'y');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('3', 'rom', 400, 'x');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('4', '101,jes,rom,101,3456,xdr', 100, 'x');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('5', '333', 100, 'z');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('7', 'yo, po', 40, 'w');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('8', 'ro,yo,go', 30, 'w');
Insert into TOAST
(KEY1, VALUESTRING1, MEASURE1, KEY2)
Values
('9', 'go', 20, 'w');
COMMIT;
===========
key1 key2 VALUESTRING1 total
4 x rom 600
6 y rom 300
5 z 333 100
9 w yo-go 90 ---yo is common in (7, and go is common in (8,9) but both are in key2 "w", so we concatenate and sum.
|
|
|
|
Goto Forum:
Current Time: Thu Mar 23 22:24:04 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
