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 ?

PLEASE DO NOT USE ALL UPPER-CASE. IT MAKES IT LOOK LIKE YOU ARE SHOUTING AT THE TOP OF YOUR LUNGS, OR ELSE THAT YOU ARE VERY IGNORANT. — John Saunders, Aug 02 '10 at 19:23
Can you give us some more info on your table, like what fields you have, the format, if there are ALWAYS 3 comment lines per record, etc. — JNK, Aug 02 '10 at 19:26
show how the data looks in the CSV file or in the database table, the excel data view is worthless. also show the query used to populate the excel file — KM., Aug 02 '10 at 19:28
what do you mean by "make a single record in table". Do you mean in excel this are 3 rows and you need it in one? — hol, Aug 02 '10 at 19:29
@Jurgen : Yes, That's exactly is my requirement. @JNK : Table Fields are as follow Ro_No(nvarchar),Comments (nvarchar),Amount(decimal),ID(int),desc(nvarchar).The Excel file has multiline comment for a single record,its not necessary that it would be 3 always.Let me know if you need any other clarification. @JOHN : I didn't mean to show my behaviour,it's an urgent issue so didn't pay attention on writing skill. — Swapnil, Aug 02 '10 at 19:57
@Swapnil: You may want to update your question to add this piece of information in it instead of inside a comment. It might prove that you are willing to help people that wants to help you. — ereOn, Aug 02 '10 at 20:27
An article with different techincs: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ — zmische, Aug 02 '10 at 20:43
As for sql approach see answers 1-3 (by vote): http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-mssql-function-to-join-multiple-rows-from-a-subquery — zmische, Aug 02 '10 at 20:47

score 0 · Answer 1 · answered Aug 02 '10 at 19:29

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.

KM. · Answer 2 · 2010-08-02T20:30:47.103

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)

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

2 Answers2