Home » SQL & PL/SQL » SQL & PL/SQL » Joining multiple rows and creating multiple columns
Joining multiple rows and creating multiple columns [message #671270] Mon, 20 August 2018 07:58 Go to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi all,

I have table in which I have some data. I need to combine two rows on the basis of SRC_REL_VAL column and also wants to generate new columns.

I think this could be done using pivot/unpivot but I am not able to get how to do it .

Output result is attached in excel . In current output both SOURCE_SYS_ID
REL_SOURCE_SYS_ID have same gold id but it could be different also.

Everything related to table is shared . If any thing else is required please let me know.


CREATE TABLE REVIEW_DATA
(
  RULE               VARCHAR2(100 CHAR),
  MATCH_GROUP_ID     NUMBER,
  PRIORITYSCORE      NUMBER,
  SOURCE_SYS_CD      VARCHAR2(20 CHAR),
  REL_SOURCE_SYS_CD  VARCHAR2(20 CHAR),
  SRC_REL            VARCHAR2(41 CHAR),
  SRC_REL_VAL        VARCHAR2(17 BYTE),
  SOURCE_SYS_IDS     VARCHAR2(20 CHAR),
  GOLD_ID            NUMBER
)

  
begin 
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)', 1, 680, 'SITE', 'SITE', 
    '1039698~416581', 'SOURCE_SYS_ID', '1039698', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)', 1, 680, 'SITE', 'SITE', 
    '1039698~416581', 'REL_SOURCE_SYS_ID', '416581', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)+E(CTRY_CD)', 1, 770, 'SITE', 'SITE', 
    '1039698~992894', 'SOURCE_SYS_ID', '1039698', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)+E(CTRY_CD)', 1, 770, 'SITE', 'SITE', 
    '1039698~992894', 'REL_SOURCE_SYS_ID', '992894', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_HQ_NM)', 1, 660, 'SITE', 'SITE', 
    '1039698~454932', 'SOURCE_SYS_ID', '1039698', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_HQ_NM)', 1, 660, 'SITE', 'SITE', 
    '1039698~454932', 'REL_SOURCE_SYS_ID', '454932', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(FFN)', 1, 460, 'SITE', 'SITE', 
    '421185~416581', 'SOURCE_SYS_ID', '421185', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(FFN)', 1, 460, 'SITE', 'SITE', 
    '421185~416581', 'REL_SOURCE_SYS_ID', '416581', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(FFN)+E(CTRY_CD)', 1, 500, 'SITE', 'SITE', 
    '421185~992894', 'SOURCE_SYS_ID', '421185', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(FFN)+E(CTRY_CD)', 1, 500, 'SITE', 'SITE', 
    '421185~992894', 'REL_SOURCE_SYS_ID', '992894', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_HQ_NM)', 1, 660, 'SITE', 'SITE', 
    '410250~454932', 'SOURCE_SYS_ID', '410250', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_HQ_NM)', 1, 660, 'SITE', 'SITE', 
    '410250~454932', 'REL_SOURCE_SYS_ID', '454932', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)', 1, 680, 'SITE', 'SITE', 
    '416581~503398', 'SOURCE_SYS_ID', '416581', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)', 1, 680, 'SITE', 'SITE', 
    '416581~503398', 'REL_SOURCE_SYS_ID', '503398', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)+E(CTRY_CD)', 1, 770, 'SITE', 'SITE', 
    '992894~503398', 'SOURCE_SYS_ID', '992894', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)+E(CTRY_CD)', 1, 770, 'SITE', 'SITE', 
    '992894~503398', 'REL_SOURCE_SYS_ID', '503398', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)+E(CTRY_CD)', 1, 770, 'SITE', 'SITE', 
    '810793~860421', 'SOURCE_SYS_ID', '810793', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)+E(CTRY_CD)', 1, 770, 'SITE', 'SITE', 
    '810793~860421', 'REL_SOURCE_SYS_ID', '860421', 1691134);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)', 1, 680, 'SITE', 'SITE', 
    '691581~922433', 'SOURCE_SYS_ID', '691581', 1686735);
Insert into REVIEW_DATA
   (RULE, MATCH_GROUP_ID, PRIORITYSCORE, SOURCE_SYS_CD, REL_SOURCE_SYS_CD, 
    SRC_REL, SRC_REL_VAL, SOURCE_SYS_IDS, GOLD_ID)
 Values
   ('E(ST_B_NM)', 1, 680, 'SITE', 'SITE', 
    '691581~922433', 'REL_SOURCE_SYS_ID', '922433', 1792130);
    
 commit;
end ;


  • Attachment: output.csv
    (Size: 8.33KB, Downloaded 195 times)
Re: Joining multiple rows and creating multiple columns [message #671272 is a reply to message #671270] Mon, 20 August 2018 08:21 Go to previous messageGo to next message
Bill B
Messages: 1907
Registered: December 2004
Senior Member
His csv file is a binary application. I would not download it. Do a layout of what you want. almost no one on this site will download and open a binary file.
Re: Joining multiple rows and creating multiple columns [message #671274 is a reply to message #671270] Mon, 20 August 2018 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A CSV file is NOT an Excel file.
Your file is NOT a CSV file, it is a Bill said a binary file which most people can't or don't want to upload.

There is no need to post such file, post the result of what you want, using code tags and aligning the columns, and explain with words the result you want:

Quote:
I need to combine two rows on the basis of SRC_REL_VAL column
OK, what basis?


Quote:
In current output both SOURCE_SYS_ID
REL_SOURCE_SYS_ID have same gold id but it could be different also.
OK, it could be same or not, this is of great help to know how to build a solution.

Re: Joining multiple rows and creating multiple columns [message #671282 is a reply to message #671274] Tue, 21 August 2018 00:51 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi All,
I did not know this that we never use attachment as reference . I am sharing sample output result which I want.

RULE       MATCH_GRP_ID SCORE  SOURCE_SYS_CD REL_SOURCE_SYS_CD SOURCE_SYS_ID gold_id REL_SOURCE_SYS_ID rel_gold_id
E(ST_B_NM)   1           680     SITE          SITE             1039698      1686735  416581             1686735

E(ST_B_NM)
+E(CTRY_CD)  1           770     SITE          SITE             1039698      1686735  992894             1686735

[Updated on: Tue, 21 August 2018 01:06]

Report message to a moderator

Re: Joining multiple rows and creating multiple columns [message #671283 is a reply to message #671282] Tue, 21 August 2018 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
and explain with words the result you want:
Re: Joining multiple rows and creating multiple columns [message #671284 is a reply to message #671283] Tue, 21 August 2018 01:08 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi all,

I have shared output for two SRC_REL
1039698~416581
1039698~992894
Re: Joining multiple rows and creating multiple columns [message #671285 is a reply to message #671283] Tue, 21 August 2018 01:09 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
I have explained it by stating I need to combine two rows on the basis of SRC_REL_VAL column and also wants to generate new columns.
Re: Joining multiple rows and creating multiple columns [message #671286 is a reply to message #671285] Tue, 21 August 2018 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 20 August 2018 15:27

Quote:
I need to combine two rows on the basis of SRC_REL_VAL column
OK, what basis?


Quote:
In current output both SOURCE_SYS_ID
REL_SOURCE_SYS_ID have same gold id but it could be different also.
OK, it could be same or not, this is of great help to know how to build a solution.

Quote:
and also wants to generate new columns

OK, which ones? What are their specification?

Explain each column of each row of the output you gave.

[Updated on: Tue, 21 August 2018 01:19]

Report message to a moderator

Re: Joining multiple rows and creating multiple columns [message #671297 is a reply to message #671286] Tue, 21 August 2018 05:19 Go to previous messageGo to next message
purnima1
Messages: 76
Registered: June 2014
Member
Hi All,
I have resolved this issue . Following query is giving me desired result

 SELECT 
         rank() over (partition by MATCH_GROUP_ID order by src_rel) rnk ,
         MATCH_GROUP_ID,
         PRIORITYSCORE,
         MAX (SOURCE_SYS_ID) source_sys_id,
         MAX (GOLD_ID) gold_id,
         MAX (SOURCE_SYS_CD) source_sys_cd,
         MAX (REL_SOURCE_SYS_ID) rel_source_sys_id,
         MAX (REL_GOLD_ID) rel_gold_id,
         MAX (REL_SOURCE_SYS_CD) rel_source_sys_cd
    FROM (SELECT src_rel,
                 rule,
                 match_group_id,
                 PRIORITYSCORE,
                 CASE
                    WHEN src_rel_val = 'SOURCE_SYS_ID' THEN source_sys_ids
                 END
                    source_sys_id,
                 CASE WHEN src_rel_val = 'SOURCE_SYS_ID' THEN gold_id END
                    gold_id,
                 source_sys_cd,
                 CASE
                    WHEN src_rel_val = 'REL_SOURCE_SYS_ID' THEN source_sys_ids
                 END
                    rel_source_sys_id,
                 CASE WHEN src_rel_val = 'REL_SOURCE_SYS_ID' THEN gold_id END
                    rel_gold_id,
                 rel_source_sys_cd
            FROM  REVIEW_DATA)
            
            GROUP BY src_rel,
         rule,
         match_group_id,
         PRIORITYSCORE

Re: Joining multiple rows and creating multiple columns [message #671306 is a reply to message #671297] Tue, 21 August 2018 07:13 Go to previous message
Michel Cadot
Messages: 66684
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you explain how we could know the following from what you posted:
       CASE
                    WHEN src_rel_val = 'SOURCE_SYS_ID' THEN source_sys_ids
                 END
                    source_sys_id,
                 CASE WHEN src_rel_val = 'SOURCE_SYS_ID' THEN gold_id END
                    gold_id,
                 source_sys_cd,
                 CASE
                    WHEN src_rel_val = 'REL_SOURCE_SYS_ID' THEN source_sys_ids
                 END
                    rel_source_sys_id,
                 CASE WHEN src_rel_val = 'REL_SOURCE_SYS_ID' THEN gold_id END
                    rel_gold_id,
                 rel_source_sys_cd
Previous Topic: Strange Issue in executing procedure
Next Topic: Relation between two gold
Goto Forum:
  


Current Time: Thu Nov 14 18:05:20 CST 2019