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 Go to next message
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

 

output expected is (groupby on common string, which is 'rom' here, and present the data on the latest key of the common string (6 here) and total from previous keys having the common string (1,2,3,4,6 total to 900). 5 doesnot have any common string with others so report that separately.

Key | valuestring | total

=========================================

6 | ‘rom’ | 900

5 | '333,111,tom' | 100




Re: group by common string [message #684002 is a reply to message #684001] Wed, 17 March 2021 19:02 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
your data model is fatally flawed. Your comma-delimited string should be broken into either separate columns or (more likely) separate rows in a child table. Design your table to Third Normal Form and your solution will become trivial.
Re: group by common string [message #684003 is a reply to message #684002] Wed, 17 March 2021 19:32 Go to previous messageGo to next message
yashi7777
Messages: 42
Registered: March 2016
Member
but how do i even group by with a common string within an array string list. Is there a way. If i even do a data model , can you suggest please
Re: group by common string [message #684010 is a reply to message #684003] Thu, 18 March 2021 09:25 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
yashi7777 wrote on Wed, 17 March 2021 19:32
but how do i even group by with a common string within an array string list. Is there a way. If i even do a data model , can you suggest please
I can't tell you what your data model should be without knowing anything about the data - it's business meaning. I just know that comma-separated lists in a single column break every rule of the relational model.
Re: group by common string [message #684012 is a reply to message #684010] Thu, 18 March 2021 09:44 Go to previous messageGo to next message
yashi7777
Messages: 42
Registered: March 2016
Member
Sure. 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.
Re: group by common string [message #684013 is a reply to message #684012] Fri, 19 March 2021 10:41 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
yashi7777 wrote on Thu, 18 March 2021 15:44
Sure. 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
Enclosing column value between commas prevents (false) match if longer value contained the common one.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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>
SY.
Re: group by common string [message #684015 is a reply to message #684014] Fri, 19 March 2021 13:15 Go to previous messageGo to next message
yashi7777
Messages: 42
Registered: March 2016
Member
Thank you so much to both of you. Was in dire need to get this worked out.
Re: group by common string [message #684054 is a reply to message #684014] Mon, 29 March 2021 20:49 Go to previous message
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;
output
===========
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,Cool and go is common in (8,9) but both are in key2 "w", so we concatenate and sum.

Previous Topic: Analytical functions
Next Topic: query display like aging
Goto Forum:
  


Current Time: Fri Mar 29 07:28:04 CDT 2024