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
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
No comments:
Post a Comment