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 |
|
purnima1
Messages: 79 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 1594 times)
|
|
|
|
Re: Joining multiple rows and creating multiple columns [message #671274 is a reply to message #671270] |
Mon, 20 August 2018 08:27 |
|
Michel Cadot
Messages: 68653 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 #671286 is a reply to message #671285] |
Tue, 21 August 2018 01:19 |
|
Michel Cadot
Messages: 68653 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 |
|
purnima1
Messages: 79 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 |
|
Michel Cadot
Messages: 68653 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
|
|
|
Goto Forum:
Current Time: Thu May 23 10:19:59 CDT 2024
|