How to convert a set of rows to a comma delimited list
The problem is how to convert:
dept lastname -------- --------------- A00 HAAS A00 LUCCHESSI A00 O CONNELL C01 KWAN C01 QUINTANA to dept lastnames ------ -------------------------- A00 HAAS,LUCCHESSI B01 NULL C01 KWAN,QUINTANA,NICHOLLS
Data used
Departments
USE sql911;
go
SELECT workdept, lastname
FROM emps;
go
go
SELECT workdept, lastname
FROM emps;
go
workdept lastname -------- --------------- A00 HAAS NULL THOMPSON C01 KWAN E01 GEYER D11 STERN D21 PULASKI E11 HENDERSON NULL SPENSER A00 LUCCHESSI A00 O CONNELL C01 QUINTANA C01 NICHOLLS NULL ADAMSON D11 PIANKA D11 YOSHIMURA D11 SCOUTTEN D11 WALKER D11 BROWN D11 JONES NULL LUTZ D21 JEFFERSON D21 MARINO D21 SMITH D21 JOHNSON D21 PEREZ E11 SCHNEIDER E11 PARKER E11 SMITH E11 SETRIGHT NULL MEHTA E21 LEE E21 GOUNOT (32 row(s) affected)
Converting a set of rows to a comma delimited list
SELECT dep.deptno,
empnos = REPLACE((
SELECT lastname AS [DATA()]
FROM emps
WHERE emps.workdept = dep.deptno
ORDER BY empno
FOR xml PATH ('')), ' ', ',')
FROM department dep
ORDER BY dep.deptno;
go
empnos = REPLACE((
SELECT lastname AS [DATA()]
FROM emps
WHERE emps.workdept = dep.deptno
ORDER BY empno
FOR xml PATH ('')), ' ', ',')
FROM department dep
ORDER BY dep.deptno;
go
deptno empnos ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- A00 HAAS,LUCCHESSI,O,CONNELL B01 NULL C01 KWAN,QUINTANA,NICHOLLS D01 NULL D11 STERN,PIANKA,YOSHIMURA,SCOUTTEN,WALKER,BROWN,JONES D21 PULASKI,JEFFERSON,MARINO,SMITH,JOHNSON,PEREZ E01 GEYER E11 HENDERSON,SCHNEIDER,PARKER,SMITH,SETRIGHT E21 LEE,GOUNOT (9 row(s) affected)
- This only applies to Microsoft SQL Server 2005.
- This relies on XML features that does not exist in SQL Server 2000 or SQL Server 7.
- Please note the problem:
workdept lastname -------- --------------- A00 O CONNELL deptno empnos ------ --------------------------------- A00 HAAS,LUCCHESSI,O,CONNELL
- The name O CONNELL became O,CONNELL
- All the spaces are replaced with , by REPLACE(..., ' ', ',')
- This is only 1 solution, you could do the same with cursors and loops.
Explanations
- FOR XML PATH (''):
- converts the rows matching 1 department to 1 line.
- (''): Do NOT use the XML tags. XML normally to <row> for each entry.
- empnos = REPLACE(...,' ',','): replaces all the spaces of the string with commas.
- AS [DATA()]: use the actual data (the lastnames).
