How to combine multiple value to one column?

[lydro]

hello, I have a problem to output multiple value to one column in datagrid in vb.net.

here is the column name in the database table
street_number1, street_name1, street_number2, street_name2, street_number3, street_name3, street_number4, street_name4, street_number5, street_name5

In the vb application, bind to two colunm(street#,street name) in datagrid. how can I show them like below?

entity_id street# street name
01101 1 XXX
2 XXX
3 XXX
4 XXX
5 XXX

anybody can help??

 

[ca8msm]

You could do it in your SQL before it is bound. You don't say what type of db you are using but in Oracle you could do:

SELECT STREET_NUMBER1||' '||STREET_NAME1 AS FULLSTREET FROM MYTABLE

Depending what database you are using the concatenation syntax may be different but hopefully you get the idea.


----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[lydro]

I'm dealing with microsoft sql 7.0. The hard thing is how to tell it to seperate two more address in next line in same column. any idea?

 

[ca8msm]

Just concatenate them in the SQL String and bind the concatenated field to one column like I showed above.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[bigfoot]

Like ca8msm says.

Select StreetNumber1 + ' ' + StreetName1 As Address1 From myTable Where...

This works for me.

 

[bigfoot]

Sorry, I made a mistake. Trim them inline too.

Select LTrim(RTrim(StreetNumber1)) + ' ' + LTrim(RTrim(StreetName1)) As Address1 From myTable Where...

 

[lydro]

name address email company
XX address1 XX XX
address2 XX
address3
address4
address5
XX address1 XX XX
XX
XX address1 XX XX
address5
XX address4 XX XX

here is the output in the datagrid it should shows. any idea to do it?

if I use
Select LTrim(RTrim(StreetNumber1)) + ' ' + LTrim(RTrim(StreetName1)) As Address1 From myTable Where...
how can I bind all address in one column to datagrid?