Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Find Duplicate value in different columns of same row

ponicpoolJul 2 2018 — edited Jul 3 2018

Hello,

I would like to return rows where same value is repeated in all the columns in a row, i.e. not duplicate rows, but duplicate columns.

The expected output is below, first and second rows are having Usable and Manuf values are getting repeated. The third row is having null getting repeated.

Capture2.JPG

Please note that I am using a legacy database having version 8.

The below is the table structure and sample data. Appreciate any help or insight.

CREATE TABLE product_info

(

product_seg1 VARCHAR2 (25),

product_seg2 VARCHAR2 (25),

product_meaning1 VARCHAR2 (25),

product_meaning2 VARCHAR2 (25),

product_meaning3 VARCHAR2 (25),

product_meaning4 VARCHAR2 (25),

product_meaning5 VARCHAR2 (25),

product_meaning6 VARCHAR2 (25)

);

SET DEFINE OFF;

Insert into PRODUCT_INFO

(PRODUCT_SEG1, PRODUCT_SEG2, PRODUCT_MEANING1, PRODUCT_MEANING2, PRODUCT_MEANING3,

PRODUCT\_MEANING4, PRODUCT\_MEANING5, PRODUCT\_MEANING6)

Values

('CAL', '2356', 'Usable', 'Usable', 'Perishable',

'In Stock', 'Usable', 'Manuf.');

Insert into PRODUCT_INFO

(PRODUCT_SEG1, PRODUCT_SEG2, PRODUCT_MEANING1, PRODUCT_MEANING2, PRODUCT_MEANING3,

PRODUCT\_MEANING4, PRODUCT\_MEANING5, PRODUCT\_MEANING6)

Values

('DISP', '4590', 'Manuf.', 'Checked', 'Perishable',

'In Stock', 'Usable', 'Manuf.');

Insert into PRODUCT_INFO

(PRODUCT_SEG1, PRODUCT_SEG2, PRODUCT_MEANING1, PRODUCT_MEANING3, PRODUCT_MEANING4,

PRODUCT\_MEANING5)

Values

('MARKS', '8924', 'Perishable', 'In Stock', 'Manuf.',

'Tested');

Insert into PRODUCT_INFO

(PRODUCT_SEG1, PRODUCT_SEG2, PRODUCT_MEANING1, PRODUCT_MEANING2, PRODUCT_MEANING3,

PRODUCT\_MEANING4, PRODUCT\_MEANING5, PRODUCT\_MEANING6)

Values

('BKLD', '5871', 'In Stock', 'Tested', 'Checked',

'Usable', 'Perisable', 'Manuf.');

COMMIT;

This post has been answered by mathguy on Jul 2 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 31 2018
Added on Jul 2 2018
10 comments
1,565 views