Wednesday, September 26, 2012

T-SQL Concatenate all row values

Many time you may need to get list of values from a database table and form a concatenated string (Comma separated).  I see still some programmer use cursor to do that.  we have simple technique to achieve this.

DECLARE @T VARCHAR(MAX)
SET @T = ''

SELECT @T = @T + COLUMN_NAME + ', '
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Metadata' AND TABLE_SCHEMA = 'DBO'
 
SELECT @T

The @T variable will hold all row values separated by comma.

No comments:

Post a Comment

Managing SQL Role

Recently, we had a scenario, where we need a simple SQL Role which gets read access to bunch of views.  While it was easy to create the vie...