title: Dynamically concatenate all columns in table
body: <p>I have searched on the Internet and found a good example: <a href="https://stackoverflow.com/questions/13761337/how-to-concatenate-all-columns-in-a-select-with-sql-server">link</a></p>
  
  <p>It is useful, but I want the output like: column1value1, column2value2 ...</p>
  
  <p>table:</p>
  
  <pre><code>create table test1(id int, name varchar(10))
  insert into test1 values(1,'Tom'),(2,'John'),(3,'Alice')
  select * from test1
  </code></pre>
  
  <p>I tried:</p>
  
  <pre><code>declare @s varchar(max)
  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 = 'test1'
  
  EXEC( 'SELECT '' ''+' + @s + ')+' + ''' '' FROM test1')
  </code></pre>
  
  <p>the result:</p>
  
  <pre><code>1,Tom
  2,John
  3,Alice
  </code></pre>
  
  <p>But I want the output:</p>
  
  <pre><code>id1,NameTom
  id2,NameJohn
  id3,NameAlice
  </code></pre>
  
  <p>How can I rewrite my query? I'm SQL Server 2019. The example is easy, in fact, there are hundreds of columns.</p>