Introduction

During development we come across a scenario several times, where we have to merge multiple SQL table rows into a single row. The blog explains how this can be achieved using SQL STUFF function. Though the code it very simple yet it is very useful.

Example

Creating a dummy table to hold the data

DECLARE @TABLE AS TABLE(Sampletext NVARCHAR(50))

INSERT INTO @TABLE VALUES('THIS IS LINE 1')
INSERT INTO @TABLE VALUES('THIS IS LINE 2')
INSERT INTO @TABLE VALUES('THIS IS LINE 3')
INSERT INTO @TABLE VALUES('THIS IS LINE 4')
INSERT INTO @TABLE VALUES('THIS IS LINE 5')

SELECT * FROM @TABLE

Here is result

Sample Text

THIS IS LINE 1

THIS IS LINE 2

THIS IS LINE 3

THIS IS LINE 4

THIS IS LINE 5

Using STUFF function to merge the rows

SELECT STUFF((SELECT SampleText + ' \r\n ' FROM @TABLE FOR XML PATH ('')),1,0,'') as Sampletext

Here is result

Sample Text

THIS IS LINE 1 \r\n THIS IS LINE 2 \r\n THIS IS LINE 3 \r\n THIS IS LINE 4 \r\n THIS IS LINE 5 \r\n

Summary

In this blog, I discussed how we can merge several rows from a SQL table to a single row. This is very useful when we have to summarize the activity performed at different time frame. Let say you have a particular player who has played several games within a specific time frame and you have logged the detail of each game in a table. Now at the end of year you have to generate a report for all the players and their played games detail in a row. In that case this query could be a good option.