-1

How to combine the multiple lines of data of a single column of data base into one line while retrieving or after retrieving. I am using JDBC to retrieve the data.

Below is example

Column address is stored as below in database and when retrieved and populated in Excel also it is populated in different rows of the column (different cells)

plot-22, xyz
Bangalore
Karnataka
521638

after retrieving the data from data base I have to put it in a excel file as below in one cell

plot-22, xyz Bangalore Karnataka 521638

I have tried many ways but not able remove the escape line and combine into one line.

I am using SQL Server database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jay
  • 23
  • 4
  • It would be better if you could also add what you have tried. – Sneha Jul 10 '17 at 11:57
  • add your sample data with table structure model –  Jul 10 '17 at 12:20
  • Are these plot-22, xyz are column names ? –  Jul 10 '17 at 12:21
  • @Sneha - I have tried below code but not working if(coulumn24!=null) { String nline = coulumn24.replace("\\n",""); fw.append(nline); } – Jay Jul 10 '17 at 12:26
  • @Srini - plot22,xyz is the value in one row of the column address, table look like below Name ID Address sam 1234 plot-22, xyz Bangalore Karnataka 521638 – Jay Jul 10 '17 at 12:29

3 Answers3

1

You can use STUFF function, it's a bit tricky and you have to care about the order in which you have to put all the records that form the address. Something like this can work:

declare @addr varchar(MAX)

SET @addr = STUFF( (SELECT ',' + [Address] FROM Table_1 WHERE Cust_ID = 1 
ORDER BY sub_id FOR XML PATH('')    ), 1, 1, '') 

print @dir

This thread is very useful to understand how stuff works:

How Stuff and 'For Xml Path' work in Sql Server

Sergio Prats
  • 1,043
  • 1
  • 14
  • 19
0

Your answer will be easy like: A PIVOT used to rotate the data from one column into multiple columns

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot

Esteban P.
  • 2,789
  • 2
  • 27
  • 43
0

I have got the solution. We need to append double quote (")to the string to remove next line character

{
String values = rs.getString(j);
char ch = '"';
String nvalues = ch + values + ch; // To escape the next line character

}
Jay
  • 23
  • 4