Display Child Rows as a Column With T-SQL (SQLServer)

It is often required to display data as a single column, especially data from child tables. This example looks at displaying a comma seperated list of data from a child table as a column in each row.

Senerio: Two tables, dept and emp, which stores departments and employees respectively. We want to display a comma seperated list of employee names as a column on the department record.

CREATE TABLE dept
( deptNo INT,
deptName VARCHAR(30) );

CREATE TABLE emp
( empNo INT,
deptNo INT,
empName VARCHAR(30) );

INSERT INTO dept VALUES (1,'Accounts');
INSERT INTO dept VALUES (2,'Sales');
INSERT INTO dept VALUES (3,'Operations');

INSERT INTO emp VALUES (1,1,'Frank');
INSERT INTO emp VALUES (2,1,'Jane');
INSERT INTO emp VALUES (3,2,'Joe');
INSERT INTO emp VALUES (4,2,'Harry');
INSERT INTO emp VALUES (5,2,'Sarah');
INSERT INTO emp VALUES (6,3,'Nate');
INSERT INTO emp VALUES (7,3,'Buddy');

To add the emp data as a column we include a select statement as a column. To make the select statement return one row, rather than each record as a new row, we use the "FOR XML PATH" clause.

SELECT d.deptNo,
d.deptName,
( SELECT (CASE WHEN ROW_NUMBER()OVER(ORDER BY e.empName)=1 THEN '' ELSE ',' END)+e.empName
FROM emp e
WHERE e.deptNo = d.deptNo
ORDER BY e.empName
FOR XML PATH('') ) employees
FROM dept d;

deptNo deptName employees
------ -------- ---------
1 Accounts Frank,Jane
2 Sales Harry,Joe,Sarah
3 Operations Buddy,Nate

The CASE clause checks the ROW_NUMBER value, to avoid a leading, (or trailing) comma.

Done

See Also: Oracle SQL equivilent, to display row data as a column