INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

how to combine two columns to one but with a "----" between objects.

how to combine two columns to one but with a "----" between objects.

how to combine two columns to one but with a "----" between objects.

(OP)
Hello everyone,

I have made a SQL Query string that gather data from two different columns in a database (column "ordernr" and column "CM") actually I would like to combine the columns to one column but the objects is seperated by a use "----" between from the query but I didn't succeed I tried a couple of different ways.


select mytable from blablaba
union
select mytable2 from blablaba
Error 245: Conversion failed when converting the nvarchar value 'detta är text så att ni vet.' to data type int.

that is why I have created a data set where I put the query result, my issue I have still got 2 different columns.

I have written an example loop that loops through first row and first object then next object next column same row and then so on..
(see in code below)

now I need a function that combine the two objects same row into one string. and for all the rows put them in a table.

example:

this is what I get:
Column "ordernr" column "CM"
1000 this is an example test

this is what I want:

(new table called "test")

1000 ---- this is an example test


Could someone help me?


here is my code so far:

CODE --> VB.net

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 
       Dim da As SqlDataAdapter
       Dim SQLStr As String
       Dim cnn As SqlConnection
       DataGridView1.DataSource = Nothing
       cnn = New SqlConnection(connectionString)
       cnn.Open()
 
       'Query för alla kolumner
       SQLStr = "SELECT " & _
       "tt.ordernr 'Ordernr'," & _
       "PostIt.Text as 'CM'" & _
       "FROM [Teknotrans_dev].dbo.OpusOrder as tt INNER JOIN" & _
       "[MyDB].dbo.CompanyMain as c On tt.bolagsnr = c.id INNER JOIN" & _
       "[MyDB].dbo.OpusOrderrow as ord On ord.ordernr = tt.ordernr INNER JOIN" & _
       "[MyDB].dbo.PostIt as PostIt On PostIt.ordernr = tt.ordernr INNER JOIN" & _
       "[MyDB].dbo.OrderVolvoLanguageName as snSrc ON ord.kallspraknr = snSrc.spraknr INNER JOIN" & _
       "[MyDB].dbo.OrderVolvoLanguageName as snTrg ON ord.malspraknr = snTrg.spraknr"
       da = New SqlDataAdapter(SQLStr, TTCon)
       ds2 = New DataSet
       da.Fill(ds2)
 
       DataGridView2.DataSource = ds2.Tables(0)
       Console.WriteLine(SQLStr)
 
       For Each Row As DataRow In ds2.Tables(0).Rows
           For Each Coll As DataColumn In ds2.Tables(0).Columns
               Dim s As String = Row(Coll.ColumnName).ToString()
 
               MsgBox(s)
 
           Next
       Next

   End Sub


Could someone help me? 

Thank you in advance 

RE: how to combine two columns to one but with a "----" between objects.

How about;

CODE

SQLStr = "SELECT " & _
"tt.ordernr 'Ordernr'," & _
"PostIt.Text as 'CM', " & _
"tt.ordernr + ' ---- ' + PostIt.Text as 'NewColumn' " & _
"FROM ... 

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: how to combine two columns to one but with a "----" between objects.

(OP)
I get this error:
cause its not just digits its also text.
Conversion failed when converting the varchar value ' ---- ' to data type int.

RE: how to combine two columns to one but with a "----" between objects.

(OP)
I have tried to use following stuff:

--Method 1 : Use CAST function
SELECT CAST(@i as varchar(10))

--Method 2 : Use CONVERT function
SELECT CONVERT(varchar(10),@i)

--Method 3 : Use STR function
SELECT LTRIM(STR(@i,10))

the result is both digits and also text..

RE: how to combine two columns to one but with a "----" between objects.

Try

"CAST (tt.OrderNr AS varchar(10)) + '----' + PostIt.Text AS 'New Column' " & _


If you want NewColumn you don't need 'NewColumn', just NewColumn. You need the apostrophe if there are spaces in the column name

RE: how to combine two columns to one but with a "----" between objects.

(OP)
Thank you works as a charm!

RE: how to combine two columns to one but with a "----" between objects.

The reason you were getting a problem is because you didn't understand the rules for data type precedence in SQL Server. Microsoft explains it here: https://msdn.microsoft.com/en-us/library/ms190309....

Basically, it's this:

When mixing data of differing types, SQL Server will perform an implicit (automatic) conversion. The site I posted above explains the rules. When mixing numbers and strings, SQL Server will attempt to convert to the number data type instead of the other way around.

This is why the explicit data type conversion proposed by softhemc worked.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: how to combine two columns to one but with a "----" between objects.

(OP)
Thank you for another explaination gmmastros.

I had that thought.
cause I had numerics and also digits so I can't convert to letters to numbers vise versa.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close