Turning Rows Into Columns In SQL Server

So I had a recent need to take a bunch of rows that I select and turn them into columns so I could put a string together for a specific column. What am I talking about? Well say that you have a table called Employees.

---

SELECT * FROM EMPLOYEES

---

ID	FIRST_NAME	LAST_NAME
-----------	------------------------------------	------------------------------------
1	Adrian	P
2	Ryan	S
3	Dan	W
4	Bruce	A



(4 row(s) affected)


At this point say that what you want to throw back to your application is :

“Adrian, Ryan, Dan, Bruce are online!”

So you are probably thinking cursor in the stored proc or loop on the application side. That’s where we can use a recursive trick to save ourselves a bit of time and cursor writing. Don’t worry it’s not hard.

---

DECLARE @EMPLOYEE_LIST VARCHAR(50)

SELECT @EMPLOYEE_LIST = COALESCE(@EMPLOYEE_LIST + ', ','') + FIRST_NAME
FROM EMPLOYEES

SELECT @EMPLOYEE_LIST AS EMPLOYEES_ONLINE

---

EMPLOYEES_ONLINE

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

Adrian, Ryan, Dan , Bruce

(1 row(s) affected)


That’s it no cursor or special stuff to do. It works because of the way you are assigning @EMPLOYEE_LIST to itself. Kinda like a recursive CTE… only without the CTE I have only done this in SQL Server 2005 so if it does not work in 2000, or 2008 ( although I would assume everything from 2005 should move up ) you may end up with a cursor or some loop.

Note: Watch the result set! I haven’t tried this with a few million rows, but I would assume you may bog down the CPU if you try to do this with a few thousand rows or something. The last thing you want is a few DBAs at your desk giving you the stare LOL