Merging Different Row Cells Into One In SQL

Hello learners! Hope you all are doing fine. This article is about merging different row cells into one for the purpose of reporting. Sometimes we need to customize according to the client’s requirements.

So, for this purpose, I am demonstrating this article. Let’s get started.

First, we will create a table and fill it with some random data.

Now, our table looks like this.

And what I want to achieve is,

Now, if you see, the rows are merged for one particular ID and the description for one ID is presented in a manner so that the name that belongs to one ID is shown in one cell:

That means the number and the next name with the same ID comes in the next line. Please find the below code to see how we can achieve this. We would be needing two global temporary tables to achieve our purpose. I have already described why we need to use global temporary tables rather than local temporary tables in the previous article.

The local temporary tables run within the scope of the procedure, when we try to select the data from the local temporary table outside the procedure scope it will give you an error saying ‘’Invalid Object’’. That is the reason to use the global temporary table.

We will also be using cursors to achieve this result as it is used to fetch the records one by one. Here is the code:

Go
Create Proc USP_Merge_Different_Row_Cells_Into_One
As
Begin
If Object_id('tempdb.dbo.##temp_merge') IS NOT NULL
Drop Table ##temp_merge;
If Object_id('tempdb.dbo.##temp_merge1') IS NOT NULL
Drop Table ##temp_merge1;
Create table ##temp_merge (ID int, Name varchar(Max));
Create table ##temp_merge1 (ID int, Name varchar(Max));
Insert into ##temp_merge values('','');
Declare @b Varchar(max) ,
@a Varchar(max),
@k int,
@i int = 0,
@j int = 1;
Declare S_Cur CURSOR FOR
Select ID, Name from Test_Merge
Open S_cur
Fetch Next from S_Cur into @k,@a
While @@FETCH_STATUS = 0
Begin
If(@k=@j)
Begin
Set @i = @i + 1
Set @b = Cast(@i as varchar(2)) + '.' + space(2) + @a
Update ##temp_merge
Set id = @k ,name = Name + @b + char(13)
Fetch next from S_Cur into @k, @a
End
Else If(@k <> @j)
Begin
Insert into ##temp_merge1
Select * from ##temp_merge
Set @j = @j + 1;
Update ##temp_merge
Set ID = '' ,Name = '';
Set @i = 0
End
End
Insert into ##temp_merge1
Select * from ##temp_merge
Close S_Cur
Deallocate S_CUr
Return
End

Now, we will execute the procedure and check the result.

Though the result you see does not show you the New Line character, when you copy the cell and paste in a blank space you will get what we wanted. Here is the copy result as well.

For every ID there are 3 name values in one cell. I hope this helps. You can use this logic for reporting or any other purpose that meets the logic.

Any feedback will be welcomed and I will try and improve. Many thanks for your support. Happy Learning!

4 Comments

Join the conversation! Your thoughts help the community grow.

Mahesh Chand7y ago
Thank you Rohit and welcome to C# Corner. Two suggestions. #1. Crop image so its easily readable. #2. In title of the article, you may want to add "SQL". It could be - Merge Multiple Row Cells Into One Cell in SQL. Think about as a user in Google, what would you search. Cheers!
- Rohit Kainth7y ago
  Thank you Mahesh Chand for the valuable feedback. Will keep that in mind for the upcoming articles. Much appreciated.
Rohit Kainth8y ago
Thank you so much for the encouragement. It boosts up morale and gives encouragement to do more and contribute even more.
Ankur Mistry8y ago
Practically when we need or when we have to implement something like this this will be helpful. Thanks for sharing this.