Geek Answers Handbook

How to merge all columns in select using SQL Server

I need my choice to have a template like this:

SELECT '<text> ' + tbl.* + ' </text>' FROM table tbl;

An ideal solution would be to have all columns separated by a comma to have this output:

SQL result for table 1 with two columns:

 '<text>col1, col2</text>'

SQL result for table 2 with three columns:

 '<text>col1, col2, col3</text>'

I tried using the CONCAT(...) function as follows:

 SELECT CONCAT('<text>', tbl.*, '</text>') FROM table2 tbl

But I understand that it is not so simple, because a variable number of columns.

Is there any simple solution to solve this problem?

I am using SQL Server 2008 R2.

+4
source share
4 answers

Any number of columns for a given table name ; If you need column names wrapped in <text>

 DECLARE @s VARCHAR(500) SELECT @s = ISNULL(@s+', ','') + c.name FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name = 'YourTableName' SELECT '<text>' + @s + '</text>'

SQL Fiddle Example here

 -- RESULTS <text>col1, col2, col3,...</text>

If you need to select a result set with <text> , then

 SELECT @S = ISNULL( @S+ ')' +'+'',''+ ','') + 'convert(varchar(50), ' + c.name FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name = 'YourTableName' EXEC( 'SELECT ''<text>''+' + @s + ')+' + '''</text>'' FROM YourTableName')

SQL Fiddle Example here

 --RESULTS <text>c1r1,c2r1,c3r1,...</text> <text>c1r2,c2r2,c3r2,...</text> <text>c1r3,c2r3,c3r3,...</text>
+9
source

SQL Fiddle

Setting up the MS SQL Server 2008 schema :

 create table YourTable ( ID int identity primary key, Name varchar(50), ) insert into YourTable values ('Name 1'), ('Name 2'), ('Name 3'), ('Name 4'), ('Name 5')

Request 1 :

 select ( select ( select ', '+T2.N.value('./text()[1]', 'varchar(max)') from ( select T.* for xml path(''), type ) as T1(N) cross apply T1.N.nodes('/*') as T2(N) for xml path(''), type ).value('substring(./text()[1], 3)', 'varchar(max)') for xml path('text'), type ) from YourTable as T

Results :

 | COLUMN_0 | -------------------------- | <text>1, Name 1</text> | | <text>2, Name 2</text> | | <text>3, Name 3</text> | | <text>4, Name 4</text> | | <text>5, Name 5</text> |
+3
source

I tried a more general approach

 Create Procedure P_GetConcatColumns(@tablename varchar(200),@Seperator Varchar(20),@StartTag Varchar(20),@EndTag Varchar(20),@WhereString Varchar(400),@OrderString Varchar(400)) as DECLARE @TmpTableName VARCHAR(100), @Columns NVARCHAR(4000), @SQL NVARCHAR(MAX), @GENNAME VARCHAR(100) Select @GENNAME='##'+Replace(Cast(NewID() as Varchar(40)),'-','') IF OBJECT_ID('tempdb.dbo.' + @GENNAME) IS NOT NULL BEGIN EXEC('DROP TABLE ' +@GENNAME ); END Select @SQL='SELECT TOP 1 * INTO ' +@GENNAME +' FROM ' + @tablename Exec (@SQL) SET @TmpTableName = 'tempdb.dbo.' + @GENNAME SELECT @Columns = STUFF( ( SELECT '+' + @Seperator + '+CAST(Coalesce(' + c.name + ','''') AS VARCHAR(MAX))' + CHAR(13) FROM tempdb.sys.columns c WHERE c.[object_id] = object_Id(@TmpTableName) FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') ,1,len(@Seperator)+2,'') --SET @SQL = N'SELECT ' +''''+ @StartTag +''''+'+'+ @Columns +''''+'+'+ @EndTag + ''' FROM ' + @tablename SET @SQL = N'SELECT ''' +@StartTag + '''+'+ @Columns +'+'''+ @EndTag + ''' FROM ' + @tablename +' '+Coalesce(@WhereString,'') +' '+Coalesce(@OrderString,'') Print @SQL EXEC sp_executeSQL @SQL --ExampleCall P_GetConcatColumns 'Arten',''',''','<test>','</test>','where id>1','Order by ID desc'
+1
source

Try the code below:

 SELECT Stuff( (SELECT N', ' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'YourTableName' FOR XML PATH(''),TYPE) .value('text( [1]','nvarchar(max)'),1,2,N'')
0
source

More articles:


All Articles