Select column with different aliases based on value

Question

I have a table for employee data and another one for his documents. If the document type is '1' so its number should be with alias 'Iqama', else if '2' it should be with alias 'Passport', else if '3' it should be as 'Drivinglicences', and so on.

I found a solution here

And this is my query:

SELECT emp_dta.emp_name,
    (CASE WHEN emp_doc.doc_id_id = 1 THEN emp_doc.doc_no END) Iqama,
    (CASE WHEN emp_doc.doc_id_id = 2 THEN emp_doc.doc_no END) Passport,
    (CASE WHEN emp_doc.doc_id_id = 3 THEN emp_doc.doc_no END) DrivingLicence,
    (CASE WHEN emp_doc.doc_id_id > 3 THEN emp_doc.doc_no END) OtheDocument
FROM  emp_dta 
INNER JOIN emp_doc ON emp_dta.emp_id = emp_doc.doc_emp_id
GROUP BY emp_dta.emp_id, emp_dta.emp_name, emp_doc.doc_id_id, emp_doc.doc_no
ORDER BY emp_dta.emp_name

But the result I get is like this:

  emp_name      Iqama       Passport    DrivingLicence  OtheDocument
  Employee1     2852436985  NULL        NULL            NULL
  Employee1     NULL        EC6331777   NULL            NULL
  Employee1     NULL        NULL        5589492385      NULL
  Employee1     NULL        NULL        NULL            366791111
  Employee2     2315492339  NULL        NULL            NULL
  Employee2     NULL        EC6331841   NULL            NULL
  Employee2     NULL        NULL        2315492385      NULL
  Employee2     NULL        NULL        NULL            906792486

But my requirement is to get a result like this:

  emp_name      Iqama       Passport    DrivingLicence  OtheDocument
  Employee1     2852436985  EC6331777   5589492385      366791111
  Employee2     2315492339  EC6331841   2315492385      906792486

How can I achieve this?

Gordon Linoff · Accepted Answer · 2020-03-19T12:25:09.250

1

Use aggregation. If you have only one value per doc_id_id, then:

SELECT e.emp_name,
       MAX(CASE WHEN ed.doc_id_id = 1 THEN ed.doc_no END) as Iqama,
       MAX(CASE WHEN ed.doc_id_id = 2 THEN ed.doc_no END) as Passport,
       MAX(CASE WHEN ed.doc_id_id = 3 THEN ed.doc_no END) as DrivingLicence,
       MAX(CASE WHEN ed.doc_id_id > 3 THEN ed.doc_no END) as OtheDocument
FROM emp_dta d INNER JOIN
     emp_doc ed
     ON e.emp_id = ed.doc_emp_id
GROUP BY e.emp_id, e.emp_name
ORDER BY e.emp_name;

Note that the GROUP BY columns have changed too. These define each row you want in the result set. So you don't want doc_id_id or doc_no in the GROUP BY.

Otherwise, you might want to use string_agg() instead of max().

edited Mar 19 '20 at 12:25

answered Mar 19 '20 at 11:53

Gordon Linoff

1,242,037
58
646
786

I tried both, MAX and string_agg. I just get the same results. – Dr. MAF Mar 19 '20 at 12:17
1

@Dr.MAF . . . You need to fix the `GROUP BY` as well. – Gordon Linoff Mar 19 '20 at 12:24
Yes. That is it. fixing the Group By clause fixed the issue. Thanks. – Dr. MAF Mar 19 '20 at 12:56

Select column with different aliases based on value

1 Answers1