Merging Different Row Cells Into One

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. So let’s get started.

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

Merging Row Cells

Now, our table looks like this.

Merging Row Cells

And what I want to achieve is,

Merging Row Cells 

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:

  1. a
  2. b
  3. c

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:

  1. Go  
  2. Create Proc USP_Merge_Different_Row_Cells_Into_One  
  3. As  
  4. Begin  
  5.   
  6.     If Object_id('tempdb.dbo.##temp_merge'IS NOT NULL  
  7.     Drop Table ##temp_merge;  
  8.       
  9.     If Object_id('tempdb.dbo.##temp_merge1'IS NOT NULL  
  10.     Drop Table ##temp_merge1;  
  11.   
  12.     Create table ##temp_merge (ID intName varchar(Max));  
  13.       
  14.     Create table ##temp_merge1 (ID intName varchar(Max));  
  15.       
  16.     Insert into ##temp_merge values('','');  
  17.   
  18.         Declare @b Varchar(max) ,  
  19.                 @a Varchar(max),   
  20.                 @k int,   
  21.                 @i int = 0,   
  22.                 @j int = 1;  
  23.   
  24.         Declare S_Cur CURSOR FOR  
  25.                 Select ID, Name from Test_Merge  
  26.   
  27.         Open S_cur  
  28.           
  29.         Fetch Next from S_Cur into @k,@a  
  30.   
  31.             While @@FETCH_STATUS = 0  
  32.             Begin  
  33.   
  34.                 If(@k=@j)  
  35.                 Begin  
  36.                     Set @i = @i + 1  
  37.                     Set @b = Cast(@i as varchar(2)) + '.' + space(2) + @a  
  38.                     Update ##temp_merge  
  39.                     Set id = @k ,name = Name + @b + char(13)  
  40.                     Fetch next from S_Cur into @k, @a  
  41.                 End  
  42.   
  43.                 Else If(@k <> @j)  
  44.                 Begin  
  45.                     Insert into ##temp_merge1  
  46.                     Select * from ##temp_merge  
  47.                     Set @j = @j + 1;  
  48.                     Update ##temp_merge  
  49.                     Set ID = '' ,Name = '';  
  50.                     Set @i = 0  
  51.                 End  
  52.   
  53.             End  
  54.   
  55.         Insert into ##temp_merge1  
  56.         Select * from ##temp_merge  
  57.           
  58.         Close S_Cur  
  59.           
  60.         Deallocate S_CUr  
  61.         Return  
  62. End  

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

Merging Row Cells

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.


    1. a
    2. b
    3. c

    1. a
    2. b
    3. c

    1. a
    2. b
    3. c

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!