locked
combine 2 columns as one column in sql server

    Question

  • 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)


    h2007


    • Edited by h2007 Tuesday, July 03, 2012 2:46 AM
    Tuesday, July 03, 2012 2:43 AM

Answers

  • Hi h2007,

    The code works well on my side. Here is a Screen shot:

     

    How about test my code with a new application ? I assume the ID is number, so Please add "Order by ID" to the SQL Query to make sure the last line of the datatable has the max length of ID. 


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by h2007 Thursday, July 05, 2012 2:03 AM
    Wednesday, July 04, 2012 5:19 AM
  • A combobox is not a good choice for displaying columns. What you could do is reading the data unmodified, than display them formatted with padded blanks, but you would have to use a fixed-width font like Courier New.

    Otherwise I still suggest using a listview or datagridview.


    Armin

    • Marked as answer by h2007 Thursday, July 05, 2012 2:03 AM
    Wednesday, July 04, 2012 9:49 AM
  • A combobox is not a good choice for displaying columns. What you could do is reading the data unmodified, than display them formatted with padded blanks, but you would have to use a fixed-width font like Courier New.

    Otherwise I still suggest using a listview or datagridview.


    Armin

    I agree combox is not a good choice for displaying columns. But due to space constraint i can't use listbox or datagridview. And i don't want to change much the existing program.

    But thank you very much for giving me idea of using fixed-width font. thank you.


    h2007

    • Marked as answer by h2007 Thursday, July 05, 2012 2:03 AM
    Thursday, July 05, 2012 2:03 AM

All replies

  • The question is where you display the columns. You can use a Listview (view=details) or a DataGridView with two columns. Just select the fields unmodified from the database.

    Armin

    Tuesday, July 03, 2012 2:50 AM
  • i am using combobox. I want user to select company from combobox.

    h2007

    Wednesday, July 04, 2012 2:32 AM
  • Hi h2007,

    Thanks for you post.

    As I read your post, my first mind is deal it with datatable instead of database. I will suggest you to keep the original database, and change the data in datatable of application, in this way, you can use the datatable to bind with combobox. Here is a sample with testtb (ID, name):

    Dim dt As New DataTable
        Private Sub Form8_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Using con As New SqlConnection( _
           "Data Source=.;Database=testdb;Integrated Security=true;Trusted_Connection=True;Timeout=0;max pool size=999")
                con.Open()
                Dim constr As String = "select * from testtb "
                Using cmd As New SqlCommand(constr, con)
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    dt.Load(reader)
                    DataGridView1.DataSource = dt
                End Using
            End Using
            Dim len As Integer = dt.Rows(dt.Rows.Count - 1).Item(0).ToString.Trim.Length
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim len1 As Integer = dt.Rows(i).Item(0).ToString.Trim.Length
                dt.Rows(i).Item(1) = dt.Rows(i).Item(0).ToString.Trim & Space(len - len1 + 3) & dt.Rows(i).Item(1).ToString.Trim
            Next
            ComboBox1.DataSource = dt
            ComboBox1.ValueMember = "id"
            ComboBox1.DisplayMember = "name"
        End Sub

    Pay attention that you need to use String.Trim Method to delete the space of data from database (if you use Access database, you don’t need to use it).

    If you insist to save the datatable to database, you also can use SQL Query or other things to update it.

    Hope this helps.

    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, July 04, 2012 3:59 AM
  • Hi h2007,

    Thanks for you post.

    As I read your post, my first mind is deal it with datatable instead of database. I will suggest you to keep the original database, and change the data in datatable of application, in this way, you can use the datatable to bind with combobox. Here is a sample with testtb (ID, name):

    Dim dt As New DataTable
        Private Sub Form8_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Using con As New SqlConnection( _
           "Data Source=.;Database=testdb;Integrated Security=true;Trusted_Connection=True;Timeout=0;max pool size=999")
                con.Open()
                Dim constr As String = "select * from testtb "
                Using cmd As New SqlCommand(constr, con)
                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    dt.Load(reader)
                    DataGridView1.DataSource = dt
                End Using
            End Using
            Dim len As Integer = dt.Rows(dt.Rows.Count - 1).Item(0).ToString.Trim.Length
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim len1 As Integer = dt.Rows(i).Item(0).ToString.Trim.Length
                dt.Rows(i).Item(1) = dt.Rows(i).Item(0).ToString.Trim & Space(len - len1 + 3) & dt.Rows(i).Item(1).ToString.Trim
            Next
            ComboBox1.DataSource = dt
            ComboBox1.ValueMember = "id"
            ComboBox1.DisplayMember = "name"
        End Sub

    Pay attention that you need to use String.Trim Method to delete the space of data from database (if you use Access database, you don’t need to use it).

    If you insist to save the datatable to database, you also can use SQL Query or other things to update it.

    Hope this helps.

    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Sorry but this not working. Still not aligned. It gives like following,

    co1   company1

    comp2   comapny2

    company3   company3

    co4   company4

    Can i use anything other than combobox but it should give me list to select my company.

    other than Listbox (with multiple column)? Because of space constraint i don't want to use listbox and user can't see all companies at a time to select (don't have dropdown list).

    Any other idea?


    h2007

    Wednesday, July 04, 2012 4:45 AM
  • Hi h2007,

    The code works well on my side. Here is a Screen shot:

     

    How about test my code with a new application ? I assume the ID is number, so Please add "Order by ID" to the SQL Query to make sure the last line of the datatable has the max length of ID. 


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by h2007 Thursday, July 05, 2012 2:03 AM
    Wednesday, July 04, 2012 5:19 AM
  • A combobox is not a good choice for displaying columns. What you could do is reading the data unmodified, than display them formatted with padded blanks, but you would have to use a fixed-width font like Courier New.

    Otherwise I still suggest using a listview or datagridview.


    Armin

    • Marked as answer by h2007 Thursday, July 05, 2012 2:03 AM
    Wednesday, July 04, 2012 9:49 AM
  • A combobox is not a good choice for displaying columns. What you could do is reading the data unmodified, than display them formatted with padded blanks, but you would have to use a fixed-width font like Courier New.

    Otherwise I still suggest using a listview or datagridview.


    Armin

    I agree combox is not a good choice for displaying columns. But due to space constraint i can't use listbox or datagridview. And i don't want to change much the existing program.

    But thank you very much for giving me idea of using fixed-width font. thank you.


    h2007

    • Marked as answer by h2007 Thursday, July 05, 2012 2:03 AM
    Thursday, July 05, 2012 2:03 AM
  • Hi h2007,

    The code works well on my side. Here is a Screen shot:

     

    How about test my code with a new application ? I assume the ID is number, so Please add "Order by ID" to the SQL Query to make sure the last line of the datatable has the max length of ID. 


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Hi Mark. Thank you very much for your effort. But still it is not working for me. I don't know may be the data problem??? But thank you very much.  If it is working your side still it is useful for anyone else who want to do something like this. I will still try 1 more time and let you know.

    Thanks onece again


    h2007

    Thursday, July 05, 2012 2:06 AM