Adventures in TSQL: Comma separated string from column values

  • Article

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]
GO

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

DECLARE @columnNames varchar(max) = '';

SELECT @columnNames = @columnNames + '[' + COLUMN_NAME + '], '
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tableName
    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 +
    CASE
        WHEN COLUMN_NAME = 'rowguid' THEN ''
        ELSE '[' + COLUMN_NAME + '], '
    END
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @tableName
    OPTION (FAST 1);

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