SQL Script to merge two tables

Question

I have 2 tables Table1 and cvdata table. I need an sql to join the two tables to return 5 rows of data

TABLE1
FE_KEY ID
6-K-1201 512623
6-K-1410 512624
6-K-1411 512625
6-K-1430 512785
6-K-1201 512623
6-K-1410 512624
6-K-1411 512625
6-K-1430 512785
6-K-1450 512792

CVDATA
FE_ID CV_CODE CV_VALUE CV_UOM
512623 EST_ISENTROPIC_POWER 445 KW
512624 EST_ISENTROPIC_POWER 47000 KW
512625 EST_ISENTROPIC_POWER 30000 KW
512785 EST_ISENTROPIC_POWER 70456 KW
512623 MAX_TEMP_COMPRESSION 200 DegC
512624 MAX_TEMP_COMPRESSION 170 DegC
512625 MAX_TEMP_COMPRESSION 200 DegC
512785 MAX_TEMP_COMPRESSION 150 DegC
512792 MAX_TEMP_COMPRESSION 180 DegC

Expected output

FE_KEY MAX_TEMP_COMPRESSION MAX_TEMP_COMPRESSIONUOM EST_ISENTROPIC_POWER EST_ISENTROPIC_POWERUOM
6-K-1201 200 DegC 445 KW
6-K-1410 170 DegC 47000 KW
6-K-1411 200 DegC 30000 KW
6-K-1430 150 DegC 70456 KW
6-K-1450 180 DegC NULL NULL

When I use this script the last row that has two null values are not returned

SELECT PACERLIVE.FEMAST_TAG_ALL.FE_KEY,
CASE WHEN PACERLIVE.R_CVDATA.CV_CODE = "MAX_TEMP_COMPRESSION" THEN PACERLIVE.R_CVDATA.CV_VALUE ELSE NULL END AS MAX_TEMP_COMPRESSION,
CASE WHEN PACERLIVE.R_CVDATA.CV_CODE = "MAX_TEMP_COMPRESSION" THEN PACERLIVE.R_CVDATA.CV_UOM ELSE NULL END AS MAX_TEMP_COMPRESSIONUOM ,
CASE WHEN CVDATA1.CV_CODE = "EST_ISENTROPIC_POWER" THEN CVDATA1.CV_VALUE ELSE NULL END AS EST_ISENTROPIC_POWER,
CASE WHEN CVDATA1.CV_CODE = "EST_ISENTROPIC_POWER" THEN CVDATA1.CV_UOM ELSE NULL END AS EST_ISENTROPIC_POWERUOM
FROM PACERLIVE.FEMAST_TAG_ALL FULL OUTER JOIN PACERLIVE.R_CVDATA ON PACERLIVE.FEMAST_TAG_ALL.ID = PACERLIVE.R_CVDATA.FE_ID FULL OUTER JOIN PACERLIVE.R_CVDATA AS CVDATA1 ON PACERLIVE.FEMAST_TAG_ALL.ID = CVDATA1.FE_ID
WHERE (PACERLIVE.FEMAST_TAG_ALL.FE_KEY Like "6-K-%" AND (PACERLIVE.R_CVDATA.CV_CODE = "MAX_TEMP_COMPRESSION") AND (CVDATA1.CV_CODE ="EST_ISENTROPIC_POWER"))

I get the result

FE_KEY MAX_TEMP_COMPRESSION MAX_TEMP_COMPRESSIONUOM EST_ISENTROPIC_POWER EST_ISENTROPIC_POWERUOM
6-K-1201 200 DegC 445 KW
6-K-1410 170 DegC 47000 KW
6-K-1411 200 DegC 30000 KW
6-K-1430 150 DegC 70456 KW

the last row with some null values is omitted.
6-K-1450 180 DegC NULL NULL

Please I need help on the script to output the expected result, including the row with some null values.

Answer 1

Hi @Chuks ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.

Please also refer below:

drop table if exists table1  
  
create table table1   
(FE_KEY  varchar(20),  
ID int)  
  
insert into table1 values  
('6-K-1201',512623),  
('6-K-1410',512624),  
('6-K-1411',512625),  
('6-K-1430',512785),  
('6-K-1201',512623),  
('6-K-1410',512624),  
('6-K-1411',512625),  
('6-K-1430',512785),  
('6-K-1450',512792)  
  
create table CVDATA  
(FE_ID int,  
CV_CODE varchar(20),  
CV_VALUE int,  
CV_UOM varchar(20))  
  
insert into CVDATA values  
(512623, 'EST_ISENTROPIC_POWER', 445, 'KW'),  
(512624, 'EST_ISENTROPIC_POWER',  47000 , 'KW'),  
(512625, 'EST_ISENTROPIC_POWER',  30000, 'KW'),  
(512785, 'EST_ISENTROPIC_POWER',  70456, 'KW'),  
(512623, 'MAX_TEMP_COMPRESSION',  200 , 'DegC'),  
(512624, 'MAX_TEMP_COMPRESSION',  170, 'DegC'),  
(512625, 'MAX_TEMP_COMPRESSION',  200 , 'DegC'),  
(512785, 'MAX_TEMP_COMPRESSION',  150 , 'DegC'),  
(512792, 'MAX_TEMP_COMPRESSION',  180 , 'DegC')  
  
SELECT a.FE_KEY,  
MAX(CASE WHEN b.CV_CODE = 'MAX_TEMP_COMPRESSION' THEN b.CV_VALUE ELSE NULL END) AS MAX_TEMP_COMPRESSION,  
MAX(CASE WHEN b.CV_CODE = 'MAX_TEMP_COMPRESSION' THEN b.CV_UOM ELSE NULL END)  AS MAX_TEMP_COMPRESSIONUOM ,  
MAX(CASE WHEN c.CV_CODE = 'EST_ISENTROPIC_POWER' THEN c.CV_VALUE ELSE NULL END)  AS EST_ISENTROPIC_POWER,  
MAX(CASE WHEN c.CV_CODE = 'EST_ISENTROPIC_POWER' THEN c.CV_UOM ELSE NULL END)  AS EST_ISENTROPIC_POWERUOM  
FROM table1 a   
left JOIN CVDATA b ON a.ID = b.FE_ID   
left JOIN CVDATA c ON a.ID = c.FE_ID  
WHERE a.FE_KEY Like '6-K-%'   
GROUP BY a.FE_KEY  

Output:

FE_KEY MAX_TEMP_COMPRESSION MAX_TEMP_COMPRESSIONUOM EST_ISENTROPIC_POWER EST_ISENTROPIC_POWERUOM  
6-K-1201 200 DegC 445 KW  
6-K-1410 170 DegC 47000 KW  
6-K-1411 200 DegC 30000 KW  
6-K-1430 150 DegC 70456 KW  
6-K-1450 180 DegC NULL NULL  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As Ronen says, it helps if you give us CREATE TABLE + INSERT statements with table definition and data. Also, when everything is uppercase, the code is very difficult to read.

But I note that you use FULL OUTER JOIN. I don't know why you use it, and I suspect it is not the wrong join type. There are even two full join operators, and they are not trivial to get right.

I also need to ask what product you are using. I note that you delimit you string literals with the double quote ("). In SQL Server, which is the topic for this forum, you use the single quote (') to delimit string literals. (Double quotes can be used, if you enable a legacy setting, but you should never to that.)