Concatenating Multiple Rows

Question

Concatenating Multiple Rows

GregLyon

SSCommitted

Points: 1946
More actions
December 30, 2002 at 6:33 pm

#80449
After scouring these discussion boards for a solution to concatenating multiple rows of data into a single field, I have come up with the following solution.
I use a temp table and insert rows into it. It uses a cursor though, which I have read is not desirable. (It probably gives away my programmer background too 🙂 Would you gurus kindly comment, or suggest a better solution for me?
I've used the Northwind database as it has a perfect example of data (Bosses and employees) that I'd like to concatenate.
Here's the code:
USE NORTHWIND
SET NOCOUNT ON
DECLARE @STR VarChar(1000)
DECLARE @emp INT
--Make a temp table to hold results
CREATE TABLE #tmpX (EmpNo INT, Emps VarChar(1000))
--Set up the cursor
DECLARE EMP_CURSOR CURSOR FOR
SELECT EmployeeID FROM Employees
--Add a WHERE clause here to limit result set if needed. For instance:
--WHERE EmployeeID IN
--(SELECT DISTINCT ReportsTo FROM Employees WHERE ReportsTo Is Not Null)
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR INTO @emp
--Get the first record
WHILE @@FETCH_STATUS = 0
BEGIN
--The outside select concatenates values
SELECT @STR = COALESCE(@str,'') + emps.[name] + ', '
FROM
(--emps
SELECT e.EmployeeID, e.LastName, e.FirstName, e2.[Name]
FROM EMPLOYEES e
LEFT OUTER JOIN
(--e2
SELECT b.EmployeeID,
b.FirstName + ' ' + b.LastName AS [Name],
b.Title, b.ReportsTo
FROM EMPLOYEES b
) e2
ON
e.EmployeeID = e2.ReportsTo
WHERE e.EmployeeID = @emp
) emps
INSERT INTO #tmpX select @emp as EmpNo, LEFT(@str, LEN(@str)-1) As Emps
--Clear out @STR for the next entry
SELECT @STR = ''
FETCH NEXT FROM EMP_CURSOR INTO @emp
END
--Cleanup the Cursor
CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR
--View the results
SELECT * FROM #tmpX
Drop TABLE #tmpX
Here's the result:
```
 

EmpNo       Emps

----------- -------------------------------------------------------------------

1           NULL

2           Nancy Davolio, Janet Leverling, Margaret Peacock, Steven Buchanan, Laura Callahan

3           NULL

4           NULL

5           Michael Suyama, Robert King, Anne Dodsworth

6           NULL

7           NULL

8           NULL

9           NULL
```
It works, but I've yet to try it on anything of size. As a programmer learning SQL I appreciate any and all comments. This solution is based on an idea posed by 'Mike2265' in this thread:
http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=4587&FORUM_ID=8&CAT_ID=1&Topic_Title=Aggregating%20Varchar%20fields&Forum_Title=T-SQL
--Removed code identifiers
Edited by - GregLyon on 12/31/2002 1:59:11 PM
January 2, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

As you state cursors are not desirable, however, they are not always avoidable.

Your example is very difficult to do in any other way than using a row-by-row operation (cursor or UDF), both of which generate additional overhead due to row level operation. Sometimes though there is no other was to process your data other than row-by-row.

You are also using a temporary table in your example, this has additional overhead since the temporary values are written out to the tempdb database on disk. Consider changing this to use a table variable (SQL 2000 only).

Hope this helps.

GregLyon SSCommitted Points: 1946 More actions · Answer 2

Thanks for the reply Paul. I'll look into the table variable as soon as we upgrade to sql 2000...Right now we're on SQL 7 though, so I think I'm stuck with the temp table.