2

I need to generate a output to show different remarks(from table2) for an id(from table 1), but the remark is not mandatory to be present in the table 2.

Tried Case function but it is bringing NULL values also as an extra line

Table: Employee id 

ID      Name            
100021  Bob         
100023  Tom         
100024  Jim

Table: Employee Remarks 

ID      remark  remark text     
100021  job     manager     
100021  salary  5000        
100021  dept    classic     
100023  job     CSR     
100023  salary  4000        
100024  dept    customer

Required Result 

ID      Name    Job     Salary  Dept
100021  Bob     manager 5000    classic
100023  Tom     CSR     4000    NA
100024  Jim     NA      NA      customer
GMB
  • 216,147
  • 25
  • 84
  • 135
rinson jimmy
  • 37
  • 5
  • Welcome to StackOverflow! Show your SQL that you tried. It's easier to correct something working not quite right than making blind guesses – Alex Yu Feb 01 '19 at 23:05
  • Afraid, I do not have much to go on. Are you guys able to see the screenshot attached? Please let me know. – rinson jimmy Feb 01 '19 at 23:13

2 Answers2

2

You seem to be looking for conditional aggregation :

SELECT 
    i.id,
    i.name,
    COALESCE(MAX(CASE WHEN r.remark = 'job'    THEN r.remark_text END), 'NA') AS job,
    COALESCE(MAX(CASE WHEN r.remark = 'salary' THEN r.remark_text END), 'NA') AS salary,
    COALESCE(MAX(CASE WHEN r.remark = 'dept'   THEN r.remark_text END), 'NA') AS dept
FROM 
    employee_id AS i
    INNER JOIN employee_remark r ON r.id = i.id
GROUP BY 
    i.id,
    i.name

The query works by joining the two tables together and then aggregating the results by user. In the SELECT clause, the MAX(CASE WHEN WHEN r.remark = 'job' ...) is used to pivot the rows from employee_remark into columns. The COALESCE() function detects NULL values and replaces them with string 'NA'.

GMB
  • 216,147
  • 25
  • 84
  • 135
0

Let me know if that helps

select e.*, job,salary, dept
from Employee_id e left join 
(SELECT r.id,
         MAX(CASE WHEN r.remark = 'job' THEN r.remark_text ELSE 'NA' END) AS job,
         MAX(CASE WHEN r.remark = 'salary' THEN r.remark_text ELSE 'NA' END) AS salary,
         MAX(CASE WHEN r.remark = 'dept' THEN r.remark_text ELSE 'NA' END) AS dept
    FROM Employee_Remarks r
GROUP BY r.id) rr
on e.id=rr.id
Oly
  • 302
  • 1
  • 8