Building a Delimited String with Subqueries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Building a Delimited String with Subqueries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-31 : 18:22:55

lee writes "I read Article ID # 2368 "Using COALESCE to Build Comma-Delimited String" and found it very interesting. However, I could not think of a way to use the simple approach without cursors to collapse a column with N rows to single column rowset. This method seems to be limited to one subset of rows at a time. I see no way to use this with a correlated subquery - So I conclude that I have execute a select statement for each set of rows that I would like to do this transformation on. I don't want that conclusion.

Here's the example from the original article:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

I would like to do something like:

Given the recordset (from a subquery):


ID Name   Depth  Group
----------------------
1  Home       1      1
2  About      2      1
3  Detail     3      1
4  Document   4      1
1  Home       1      2
6  Contact    2      2
7  Detail     3      2
8  Document   4      2

I would like to get to:


Name                       Group
--------------------------------
Home,About,Detail,Document     1
Home,Contact,Detail,Document   2

Using just one query instead of a slow cursor."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources