Monday, November 20, 2017

MS SQL Server - Query - Concatenate data from multiple records in a GROUP BY query

Problem: Need to find out the employees reporting to a manager and create a comma separated list of names. Data will look like table-01.











Expected output would look like:
Table-01:

Solution-01: using FOR XML PATH

-- Create table
CREATE TABLE 
#employeeManagerAssociation (managerID INT, employeeID INT, firstName VARCHAR(100), lastName VARCHAR(100))
;

-- Insert data
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (1, 101,'E1_First', 'E1_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (1,102,'E2_First', 'E2_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (2,103,'E3_First', 'E3_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (2,104,'E4_First', 'E4_Last');
INSERT INTO #employeeManagerAssociation (managerID,employeeID,firstName,lastName) VALUES (3,105,'E5_First', 'E5_Last');

SELECT 
  managerID,
  STUFF((
    SELECT ', ' + firstName + ' ' + lastName 
    FROM #employeeManagerAssociation 
    WHERE managerID = main.managerID
    FOR XML PATH('')
)--,TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #employeeManagerAssociation main
GROUP BY managerID

-- Delete temporary table

DROP TABLE #employeeManagerAssociation