SQL Combine multiple rows in single row

SQL Server: Combine multiple rows in single row

I want to discuss about Combine multiple rows in single row.

Combine multiple rows in single row may be necessary for concatenating data, reporting, exchanging data between systems and more. This can be accomplished by:

  • The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results. The SQL Server T-SQL commands used are STUFF and FOR XML.
  • The T-SQL STUFF command is used to concatenate the results together. In this example, the results are separated by a semi-colon.
  • The FOR XML option for the SELECT command has four options (i.e. RAW, AUTO, EXPLICIT or PATH) to return the results. In this example, the PATH parameter is used to retrieve the results as an XML string.

SQL Server STUFF() Function:

Before going to the examples, we need to understand the workings of the commands mentioned above. The STUFF() function puts a string in another string, from an initial position. With this we can insert, replace or remove one or more characters.

This syntax is STUFF(character_expression, start, length, replaceWith_expression):

  • character_expression: string to be manipulated
  • start: initial position to start
  • length: number of characters to be manipulated
  • replaceWith_expression: characters to be used


Use Stuff:

You can Try as Bellow:

It’s will be:

3 thoughts on “SQL Server: Combine multiple rows in single row

  1. It’s actually a cool and helpful piece of information. I’m glad that
    you just shared this helpful information with us.
    Please keep us informed like this. Thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *