Click here to Skip to main content
15,916,693 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i want to combine 2 columns as one and align 2nd col combined.

Example (i want to combine company id and name as 1 column),

coid          coname

co1           company 1

comp2         company 2

companyid3    company 3

if i simply do select coid + ' ' + coname from mytable, then it gives me result as,

co1   company 1

comp2   company 2

companyid3   company 3

i want to align 2nd col (i.e. coname to like ),

co1             company 1

comp2           company 2

companyid3      company 3


My code, (i am trying following code) - But still not aligned

i am trying to get max length of coid then add accordingly. In above e.g. add only 5 spaces to 3rd record, add 10 spaces in 2nd record and add 12 spaces in 1st. But still no use may be bucause width of individual character. Anyone have have any idea to do this.

Dim mySql As String = "declare @len1 int " & Chr(10)
mySql = mySql & "select @len1 =  max(len(coid)) from mytable" & Chr(10)
mySql = mySql & "select coid, coid + space(@len1-len(coyid)+5) + coname as coname from mytable order by coid" & Chr(10)
Dim sqladp As New SqlClient.SqlDataAdapter(mySql, _SqlCon.sqlCon)

i want to show this in combobox. So user can select from list.

1 solution

Using simulated padding will give you the desired effect.
select compId + RIGHT(REPLICATE(' ',10) + compName ,10)

I've used 10 here. You can use a number that is ideal for your column.
Share this answer
Harshada J. 3-Jul-12 23:42pm    
this one not working. It is actually overwritting first few characters. Even i five big no like 50
select compId + RIGHT(REPLICATE(' ',50) + compName ,50)
Still not aligned.
Abhinav S 4-Jul-12 1:35am    
Sorry. Try select LEFT(REPLICATE(' ',10) + compId ,10) + RIGHT(REPLICATE(' ',10) + compName ,10).
I missed the LEFT part.
Harshada J. 4-Jul-12 22:37pm    
Nope. It's not working? Something is wrong. R u sure it is replicate function? It repeats but truncate the return value.
Abhinav S 4-Jul-12 23:25pm    
LEFT and RIGHT do the truncate. REPLICATE just adds spaces.
Are getting some error?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900