共用方式為

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

Comments

  • Anonymous
    November 16, 2011
    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!
  • Anonymous
    November 16, 2011
    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.