Problem Statement:-

Emp_Details Table has four columns EmpID, Gender, EmailID and DeptID. User has to write a SQL query to derive another column called Email_List to display all Emailid concatenated with semicolon associated with a each DEPT_ID as shown below in output Table.

INPUT:


OUTPUT:
 

Table and Insert SQL Script :
CREATE TABLE Emp_Details ( EMPID int, Gender varchar, EmailID varchar(30), DeptID int) INSERT INTO Emp_Details VALUES (1001,'M','YYYYY@gmaix.com',104) INSERT INTO Emp_Details VALUES (1002,'M','ZZZ@gmaix.com',103) INSERT INTO Emp_Details VALUES (1003,'F','AAAAA@gmaix.com',102) INSERT INTO Emp_Details VALUES (1004,'F','PP@gmaix.com',104) INSERT INTO Emp_Details VALUES (1005,'M','CCCC@yahu.com',101) INSERT INTO Emp_Details VALUES (1006,'M','DDDDD@yahu.com',100) INSERT INTO Emp_Details VALUES (1007,'F','E@yahu.com',102) INSERT INTO Emp_Details VALUES (1008,'M','M@yahu.com',102) INSERT INTO Emp_Details VALUES (1009,'F','SS@yahu.com',100)

Solution 1:
SELECT DeptID, STRING_AGG (EmailID,';') WITHIN GROUP ( ORDER BY EmailID) as Email_List FROM Emp_Details GROUP BY DeptID ORDER BY DeptID;

Solution 2:
  
SELECT DeptID, STUFF(( SELECT ';', CAST(EmailID AS VARCHAR(MAX)) FROM Emp_Details B WHERE A.DeptID=B.DeptID FOR XML PATH('') ), 1, 1, '') as Email_List FROM Emp_Details A GROUP BY DeptID ORDER BY DeptID;