How to merge 3 cells content in one in excel or by using sql

Question

I have an excel csv file that i am uploading to sql server table using sqlbulkcopy,data in excel looks like this

 121 **ABCDEFG**  138.00 141   XYZ
     **HIJKLMN**
     **OPQRSTUV**     

So basically this 3 line make a single record in table,how should i merge these 3 line comments in one ?

Answer 1

I would probably do this line by line in VBA:

I'd initialize a few variables like startRow, currentRow, and lastRow

I'd create a recursive function that accepted a row number, and it would check if the NEXT rows first column was blank... if blank, it would return the current rows second column, plus the output of the same function using the next row number. if not blank, it would return the current rows second column. It would also check to see if the lastRow count had been hit.

The main loop would start at start row, and build a simple insert query, using the recursive function to get the note text for the second column, and then increment the currentRow counter. It would check the row to see if the first column was empty, and if so just continue to the next row. It would check to see if last row count has been reached.

Answer 2

change your query to something like this:

set nocount on;
declare @t table (Ro_No nvarchar(5), Comments nvarchar(20), Amount decimal(5,2))
insert into @t (Ro_No, Comments, Amount)
select '121','**ABCDEFG**' , 1.38 union
select '121','**HIJKLMN**' , 1.38 union
select '121','**OPQRSTUV**', 1.38 union
select '221','aaa'         , 2.2 union
select '221','bbb'         , 2.2 union
select '321','test3a'      , 3.2 union
select '321','test3b'      , 3.2
set nocount off

SELECT p1.Ro_No
         ,stuff(
                   (SELECT
                        ', ' + p2.Comments
                        FROM @t p2
                        WHERE p2.Ro_No=p1.Ro_No
                        ORDER BY p2.Ro_No,p2.Comments
                        FOR XML PATH('') 
                   )
                   ,1,2, ''
               ) AS All_Comments
      ,p1.Amount
      FROM @t p1
     GROUP BY 
        Ro_No, Amount

OUTPUT:

Ro_No All_Comments                              Amount
----- ----------------------------------------- ------
121   **ABCDEFG**, **HIJKLMN**, **OPQRSTUV**    1.38  
221   aaa, bbb                                  2.20  
321   test3a, test3b                            3.20  

(3 row(s) affected)