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:

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:

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.

1. 
   
1. a
2. b
3. c
2. 
   
1. a
2. b
3. c
3. 
   
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!