sql group_concat(colname SEPARATOR '?')

  Emp_details_test has four columns EmpId,Gender,EmailId and DeptId.User has to write a sql query to dervie another column called Email_list to display all emailid concatenated with semicolon associated with each DEPT ID as shown below in ouput table.

i:/p

EMPID GENDER EMAILID DeptId

1001 M YYY@gmail.com 104

1002 M ZZ@gmail.com 103

1003 F AAAA@gmail.com 102

1004 F PP@gmail.com 104

1005 M CCCC@gmail.com 101

1006 M EEE@gmail.com 100

1007 F FGH@gmail.com 102

1008 M YYY@gmail.com 102

1009 F RAm@gmail.com 100

use test;

Create table emp_concated

(

EMPID int

Gender varchar(10),

EMAILID varchar(10),

DeptId int

);

 

insert into emp_concated values (1001,"M","YYY@gmail.com",104);

insert into emp_concated values (1002,"M","ZZ@gmail.com",103);

insert into emp_concated values (1003,"F","AAAA@gmail.com",102);

insert into emp_concated values (1004,"F","PP@gmail.com",104);

insert into emp_concated values (1005,"M","CCCC@gmail.com",101);

insert into emp_concated values (1006,"M","EEE@gmail.com",100);

insert into emp_concated values (1007,"F","FGH@gmail.com",102);

insert into emp_concated values (1008,"M","YYY@gmail.com",102);

insert into emp_concated values (1009,"F","RAm@gmail.com",100);

select * from emp_concated;

# EMPID Gender EMAILID DeptId

1001 M YYY@gmail.com 104

1002 M ZZ@gmail.com 103

1003 F AAAA@gmail.com 102

1004 F PP@gmail.com 104

1005 M CCCC@gmail.com 101

1006 M EEE@gmail.com 100

1007 F FGH@gmail.com 102

1008 M YYY@gmail.com 102

1009 F RAm@gmail.com 100

select empid,group_concat(emailid SEPARATOR '?') from emp_concated group by deptid;

# empid group_concat(emailid SEPARATOR '?')

1006 EEE@gmail.com?RAm@gmail.com

1005 CCCC@gmail.com

1003 AAAA@gmail.com?FGH@gmail.com?YYY@gmail.com

1002 ZZ@gmail.com

1001 YYY@gmail.com?PP@gmail.com