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.