OraFAQ Forum: SQL & PL/SQL » Combination of string

Home » SQL & PL/SQL » SQL & PL/SQL » Combination of string (Oracle DB 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Combination of string [message #690243]

Sat, 04 January 2025 08:16

mamalik
Messages: 270
Registered: November 2008
Location: Pakistan

Senior Member

Dear All,

I want to make combination of a string column. Case is posted below.

 
CREATE TABLE TEST
(
  PRS_COD  NUMBER,
  PRS_DES  VARCHAR2(100 BYTE) 
)

SET DEFINE OFF;
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15413, 'VAT DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15414, 'REACTIVE DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15415, 'DISPERSE DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15416, 'DISPERSE VAT');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15417, 'DISPERSE REACTIVE');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15418, 'NAPHTHOL DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15419, 'DISPERSE NAPHTHOL');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15420, 'PIGMENT DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15421, 'REACTIVE DEVELOPING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15422, 'VAT DEVELOPING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15423, 'DISPERSE REDUCTION CLEARING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15424, 'DISPERSE + VAT DEVELOPING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15425, 'DISPERSE + REACTIVE DEVELOPING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15426, 'NAPHTHOL DEVELOPING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15427, 'WASHING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15428, 'FR FINISHED FABRIC WASHING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15429, 'AMMONIA TREATED FABRIC WASHING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15430, 'WEIGHT REDUCTION');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15431, 'FABRIC PRETREATMENT');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15432, 'REACTIVE DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15433, 'DISPERSE DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15434, 'ACID DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15435, 'BASIC DYEING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15436, 'FINISH');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15437, 'SPECIAL FINISH');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15438, 'FINISH + HEAT SET');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15439, 'WATER STRETCH');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15440, 'DRY BATCHING');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15441, 'HEAT SET');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15442, 'FR FINISH');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15443, 'AMMONIA FINISH');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15444, 'PENTEK');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15445, 'PEACH');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15446, 'SANFO');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15447, 'WIDTH REDUCTION');
Insert into TEST
  (PRS_COD, PRS_DES)
Values
  (15448, 'AMMONIA-01');
COMMIT;

Select * from Test;

It will show data like

PRS_COD	PRS_DES 
15413	VAT DYEING
15414	REACTIVE DYEING
15415	DISPERSE DYEING
15416	DISPERSE VAT

I want to write query that should make combination for all like,
VAT DYEING+REACTIVE DYEING,
VAT DYEING+DISPERSE DYEING
VA DYEING+DISPERSE VAT
VAT DYEING+REACTIVE DYEING+DISPERSE DYEING
VAT DYEING+REACTIVE DYEING+DISPERSE DYEING+DISPER VAT

REACTIVE DYEING + DISPERSE DYEING
REACTIVE DYEING + DISPERSE DYEING + DISPERSE VAT

DISPERSE DYEING+DISPERSE VAT

ETC.

Report message to a moderator

Re: Combination of string [message #690244 is a reply to message #690243]

Sat, 04 January 2025 13:47

Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA

Senior Member

It is unclear from your partial results exactly what you want or if the order matters.

If you are trying to get all possible combinations of 2 or more prs_des, then you are missing some.

The following assumes that you want all possible combinations of 2 or more prs_des in order of root prs_cod, level, and connect by path of prs_cod.

-- test data:

SCOTT@orcl_12.1.0.2.0> Select * from Test ORDER BY prs_cod
  2  /

   PRS_COD PRS_DES
---------- ------------------------------
     15413 VAT DYEING
     15414 REACTIVE DYEING
     15415 DISPERSE DYEING
     15416 DISPERSE VAT

4 rows selected.

-- query:

SCOTT@orcl_12.1.0.2.0> SELECT  LTRIM (SYS_CONNECT_BY_PATH (prs_des, ' + '), ' + ') scbp_des
  2  FROM    test
  3  WHERE   LEVEL > 1
  4  CONNECT BY PRIOR prs_cod < prs_cod
  5  ORDER   BY CONNECT_BY_ROOT prs_cod,
  6  		LEVEL,
  7  		LTRIM (SYS_CONNECT_BY_PATH (prs_cod, ' + '), ' + ')
  8  /

SCBP_DES
----------------------------------------------------------------------
VAT DYEING + REACTIVE DYEING
VAT DYEING + DISPERSE DYEING
VAT DYEING + DISPERSE VAT
VAT DYEING + REACTIVE DYEING + DISPERSE DYEING
VAT DYEING + REACTIVE DYEING + DISPERSE VAT
VAT DYEING + DISPERSE DYEING + DISPERSE VAT
VAT DYEING + REACTIVE DYEING + DISPERSE DYEING + DISPERSE VAT
REACTIVE DYEING + DISPERSE DYEING
REACTIVE DYEING + DISPERSE VAT
REACTIVE DYEING + DISPERSE DYEING + DISPERSE VAT
DISPERSE DYEING + DISPERSE VAT

11 rows selected.

[Updated on: Sat, 04 January 2025 13:48]

Report message to a moderator

Re: Combination of string [message #690245 is a reply to message #690244]

Sat, 04 January 2025 22:14

mamalik
Messages: 270
Registered: November 2008
Location: Pakistan

Senior Member

Thanks a lot sir,
You got the point and this was the actual that I needed.

Report message to a moderator

Previous Topic:	Help Needed in Calculation Logic function
Next Topic:	Row generator

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 19:59:25 CST 2025