Adventures in TSQL: Comma separated string from column values

It seems that several times now I have had the requirement to create a comma separated string from column values. The latest reason for doing this was to build up a list of table column names such that a view could be generated for the table.

This is surprisingly easy to do in TSQL. One merely has to define a varchar variable and build up the comma separated string from within a SELECT statement; as this sample demonstrates:

USE [AdventureWorks]

DECLARE @schema varchar(128) = 'Sales';
DECLARE @tableName varchar (128) = 'SalesOrderHeader';

DECLARE @columnNames varchar(max) = '';

SELECT @columnNames = @columnNames + '[' + COLUMN_NAME + '], '
    OPTION (FAST 1);
IF (LEN(@columnNames) > 0) SET @columnNames = SUBSTRING(@columnNames, 1, LEN(@columnNames) -1);

PRINT @columnNames

The output from this is the @columnNames variable is:

[SalesOrderID], [RevisionNumber], [OrderDate], [DueDate], [ShipDate], [Status], [OnlineOrderFlag], [SalesOrderNumber], [PurchaseOrderNumber], [AccountNumber], [CustomerID], [ContactID], [SalesPersonID], [TerritoryID], [BillToAddressID], [ShipToAddressID], [ShipMethodID], [CreditCardID], [CreditCardApprovalCode], [CurrencyRateID], [SubTotal], [TaxAmt], [Freight], [TotalDue], [Comment], [rowguid], [ModifiedDate]

The logic for this can even get more complicated where only certain values from the table are selected for inclusion in the comma separated string.

In the case of the AdventureWorks database many tables have a column named “rowguid”. If one wanted to exclude this from the list one would write:

SELECT @columnNames = @columnNames +
        WHEN COLUMN_NAME = 'rowguid' THEN ''
        ELSE '[' + COLUMN_NAME + '], '
    OPTION (FAST 1);

The ability to define a variable value in this fashion is not well known, but nevertheless a useful feature.

Comments (2)

  1. Sri says:

    Alternate: In SQL Server Management Studio – object explorer, click on the [table], drag the columns (folder icon) to a new query window. You will see all the column values shown as comma separated values!

  2. Carl Nolan says:

    This is a shortcut to typing in the actual column names when writing queries.

    The code above presents the values from a column represented as a comma seperated list.

Skip to main content