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

Missing Date

I need to check in a table, is the all day data available, here is the handy script to check the same.. DECLARE @startdate DATETIME ...