Return union result in one row?

pppat

Hi folks,

I want to union two tables into one row per empno

SELECT EMPNO, EMAIL , ' '
FROM EMP_T

UNION ALL
SELECT EMPNO, ' ', MGREMAIL
FROM MGR_T

tables look like this:

EMP_T

EMPNO EMAIL
------- ----------
0001 emp1@123.com
0002 emp2@123.com
0003 emp3@123.com

MGR_T

EMPNO MGREMAIL
------- -----------------------------
0001 mgr1@123.com
0002 mgr2@123.com
0004 mgr3@123.com
0003 mgr4@123.com

I want the result to look like this:

EMPNO email mgremail
------- -------------------- --------------------
0001 emp1@123.com mgr1@123.com
0002 emp2@123.com mgr2@123.com
0004 mgr3@123.com
0003 emp3@123.com mgr4@123.com

Any suggestions ?
Thanks

cburnett

Expand|Select|Wrap|Line Numbers

 
select empno,

              max(email) as email,

              max(mgremail) as mgremail

     from (select empno, email , ' ' as mgremail

                   from emp_t

                 union all

                 select empno, ' ' as email, mgremail

                    from mgr_t) x

   group by empno

pppat

Nice work, many thanks

Return union result in one row?

re: Return union result in one row?

re: Return union result in one row?

Similar DB2 Database bytes

/bytes/development

/bytes/it

/bytes/about