Mr.Maze Mr.Maze - 1 month ago 5
SQL Question

Combine multiple rows into single row in a SELECT statement

I have these two tables, I will joining using EMP_PHONE_ID to get the results. The JOIN condition is a bit more
complicated, but this is just a sample to get started. When I JOIN the two tables, I'm getting 2 rows for each EMP_ID
since each EMP_ID maps to two EMP_PHONE_ID. I would like the end results to be single row the combines both PERM and TEMP values.

TABLE B

EMP_PHONE_ID || PERM_VALUE || TEMP_VALUE
1 || value1 || NULL
2 || NULL || value2
3 || value3 || NULL
4 || NULL || value4


TABLE A

EMP_ID ||EMP_PHONE_ID ||CODE
1 ||1 ||1
1 ||2 ||2
2 ||3 ||1
2 ||4 ||2




DESIRED RESULTS

EMP_ID || PERM_VALUE || TEMP_VAL
1 || value1 || value2
2 || value3 || value4

Here's my select
SELECT
CASE WHEN A.CODE = 1 THEN B.PERM_VALUE END AS PERM_VALUE1,
ASE WHEN A.CODE = 2 THEN B.PERM_VALUE END AS PERM_VALUE2
FROM TABLE A A, TABLE B B
WHERE A.EMP_PHONE_ID = B.EMP_PHONE_ID;



DESIRED RESULTS

EMP_ID || PERM_VALUE || TEMP_VAL
1 || value1 || value2
2 || value3 || value4

Answer

Inner join + Group By + Min or Max aggregate should work for you

SELECT EMP_ID,
       Min(PERM_VALUE) as PERM_VALUE,
       Min(TEMP_VALUE) as TEMP_VALUE
FROM   TABLEA A
       JOIN TABLEB B
         ON A.EMP_PHONE_ID = B.EMP_PHONE_ID
GROUP  BY EMP_ID 

If you want to display the data based on Code then use this

SELECT EMP_ID,
       Min(CASE WHEN A.CODE = 1 then PERM_VALUE end) as PERM_VALUE,
       Min(CASE WHEN A.CODE = 2 then TEMP_VALUE end) as TEMP_VALUE
FROM   TABLEA A
       JOIN TABLEB B
         ON A.EMP_PHONE_ID = B.EMP_PHONE_ID
GROUP  BY EMP_ID 

I think you are storing the data in wrong way in TABLEA

Comments