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.
To achieve this we will use For XML Path Clause and STUFF Commands.
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.
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.
Data in table like this: