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;
