1878

concat all columns in SQL Server

Joining all column values into one string with concat function is a pain, especially for a table with large numbers of columns. This script concats all columns quickly.

declare @sql varchar(max)
SELECT @sql = ISNULL( @sql+ '' + '+'',''+', '') + 'convert(varchar(50), [' + c.name + '])'
FROM sys.all_columns c join sys.tables t
ON c.object_id = t.object_id
WHERE t.name = 'herb' and c.name!='id'
EXEC( 'SELECT ' + @sql + ' FROM herb')

Result

The script generates long string value of @sql as following:

convert(varchar(50), [alias])+','+convert(varchar(50), [application])+','+convert(varchar(50), [attachment])+','+convert(varchar(50), [category])+','+convert(varchar(50), [caution])+','+convert(varchar(50), [chemical])+','+convert(varchar(50), [desc])+','+convert(varchar(50), [english])+','+convert(varchar(50), [enterdate])+','+convert(varchar(50), [extract])+','+convert(varchar(50), [form])+','+convert(varchar(50), [form1])+','+convert(varchar(50), [grow])+','+convert(varchar(50), [guijing])+','+convert(varchar(50), [image_all])+','+convert(varchar(50), [image_leaf])+','+convert(varchar(50), [image_root])+','+convert(varchar(50), [indication])+','+convert(varchar(50), [indication1])+','+convert(varchar(50), [locations])+','+convert(varchar(50), [medicalprop])+','+convert(varchar(50), [name])+','+convert(varchar(50), [notes])+','+convert(varchar(50), [origin])+','+convert(varchar(50), [pharmacology])+','+convert(varchar(50), [pinyin])+','+convert(varchar(50), [process])+','+convert(varchar(50), [source])+','+convert(varchar(50), [store])+','+convert(varchar(50), [url])+','+convert(varchar(50), [usage])

concat all columns in SQL Server

 633 total views

Author: Albert

Leave a Reply