Generating List Output in Transact-SQL
By Kalman Toth, M.Phil., M.Phil., MCDBA

May 8, 2005

Sooner or later the time arrives in the life of a SQL Server DBA or developer when he or she has to produce a list output. SQL is organized vertically for very large number of rows, horizontally limited to a relatively modest number of columns which are hard-wired.

This is the usual vertical output:

use AdventureWorks
select Name from Production.ProductCategory

Here is the vertical output:

Accessories
Bikes
Clothing
Components
Vehicles

We can save the output into a temporary table and run a WHILE loop to assemble it into a list. However, transact-SQL has a neater way of generating a list output. Here is the sample script:

use AdventureWorks
declare @List varchar(1000)
set @List = ''

select @List = @List + Name + ', '
from Production.ProductCategory

-- Remove trailing comma
if @List <> ''
set @List = substring(@List, 1, len(@List) - 1)

select @List

The list output:

Accessories, Bikes, Clothing, Components, Vehicles