Rollling up the multiple rows into a single row and column for SQL Server.

Problem:

Need a way to roll-up multiple rows into one row and one column. There is a way we can roll-up multiple rows into one row using pivot, but we need all of the data concatenated into a single column in a single row.

Solution:

To achieve this we will use For XML Path Clause and STUFF Commands.

STUFF() Function
STUFF function is used to insert a string into another string. Basically, it deletes the characters from a source string and inserts another string at the specified position.

Syntax:

STUFF(Expression,Start, Length,Replacement_expression)

Here, Expression is an expression of the character data to be modified. Start is an integer, which specifies start position in Expression to delete and insert another string (i.e. Replacement_expression) from here and length is an integer, which specifies the number of characters to be deleted. Replacement_expression is a character expression to be inserted at the start position.

Example:

Data in table like this:

Query :

Output :