1

I have 2 tables emp and emp_desc

emp table has columns empid,empname,eempaddress

emp_desc table has columns empid, empdescseqno, empdesc (for one empid we have multiple empdesc - it is not fixed)

Table 1

empid  ename   eaddress
 1      ABS      PPPPPP
 2      DSC      CCCCC

Table 2

empid  empdescseqno  empdesc
  1      1              JJJJJ
  1      2              KKKKKK
  1      3              LLLLLL
  2      1              MMMMMM
  2      2              NNNNNN

I want to retrive empid, empname and empdesc joining the two tables. However, in the resultant table, all the empdesc corresponding to one employee should be concatenated in one string.

That is, the result should be something as below:

empid    empname    empdesc
  1       ABS        JJJJJ KKKKKK LLLLLL
  2       DSC        MMMMMM NNNNNN

I need to write a generalized SELECT query resulting in the final table above.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Mayank
  • 11
  • 2
  • What database do you use? There's no solution that's portable, and SQL purists (of which, for the record I'm not one) will tell you you shouldn't be doing this in SQL anyway. – Dan Sep 21 '11 at 04:45
  • I am using DB2 database. Actually this is a input to Spring Batch Item Reader. Which requires that items come as single row (which will go in POJO) through a select query only. – Mayank Sep 21 '11 at 04:49
  • If you use Oracle or SqlServer or DB2, you can try use ListAgg() function. http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0058709.html – Thinhbk Sep 21 '11 at 04:52
  • 2
    Looks like the same question here: http://stackoverflow.com/questions/3728010/create-a-delimitted-string-from-a-query-in-db2 – Dan Sep 21 '11 at 04:52
  • I need query that can run on DB2 – Mayank Sep 21 '11 at 04:54
  • http://www.db2ude.com/?q=node/114 check this. – bniwredyc Sep 21 '11 at 05:07
  • Here is the solution : select e.empid, substr( xmlserialize( xmlagg( xmltext( concat( ', ', empdesc ) ) ) as varchar( 1024 ) ), 3 ) from empdesc desc , emp e where e.empid =desc.empid group by e.empid Thanks – Mayank Sep 21 '11 at 05:09

2 Answers2

0

IF You are using MYSQL Database then You may use this Query.

SELECT empid, ename, GROUP_CONCAT(empdesc ORDER BY empdescseqno SEPARATOR ' ') as empdesc FROM employee emp 
INNER JOIN employee_map emp_map ON emp.empid = emp_map.empid 
GROUP BY emp.empid;

This will give Result as you want.

I think you want it for DB2 then you need to find alternative functions in DB2

Kammy
  • 409
  • 1
  • 7
  • 26
0

Reproducing Mayank's answer here as he kept it in comments

select e.empid, substr( xmlserialize( xmlagg( xmltext( concat( ', ', empdesc ) ) ) as varchar( 1024 ) ), 3 ) from empdesc desc , emp e where e.empid =desc.empid group by e.empid

Thanks to Dan for pointing out the old link.

Vicky
  • 16,679
  • 54
  • 139
  • 232