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.

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
  6. If Object_id('tempdb.dbo.##temp_merge') IS NOT NULL
  7. Drop Table ##temp_merge;
  9. If Object_id('tempdb.dbo.##temp_merge1') IS NOT NULL
  10. Drop Table ##temp_merge1;
  12. Create table ##temp_merge (ID int, Name varchar(Max));
  14. Create table ##temp_merge1 (ID int, Name varchar(Max));
  16. Insert into ##temp_merge values('','');
  18. Declare @b Varchar(max) ,
  19. @a Varchar(max),
  20. @k int,
  21. @i int = 0,
  22. @j int = 1;
  24. Declare S_Cur CURSOR FOR
  25. Select ID, Name from Test_Merge
  27. Open S_cur
  29. Fetch Next from S_Cur into @k,@a
  31. While @@FETCH_STATUS = 0
  32. Begin
  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
  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
  53. End
  55. Insert into ##temp_merge1
  56. Select * from ##temp_merge
  58. Close S_Cur
  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!