Multiple SQL rows merge into single row if the id is same

Muhsinathk
Posted by Muhsinathk under Sql Server category on | Points: 40 | Views : 2849
Hi,

CREATE TABLE employee
(
LastName VARCHAR(20),
DepartmentID INT
);

INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);
INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);


SELECT e1.DepartmentID,
DepartmentID=replace((SELECT LastName AS [data()]
FROM employee e2 WHERE e2.DepartmentID=e1.DepartmentID ORDER BY DepartmentID
FOR XML PATH('')),' ',',')FROM employee e1 GROUP BY DepartmentID

Comments or Responses

Login to post response