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

[Xsi]

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:

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

 

[Andrzejek]

How about;

SQLStr = "SELECT " & _
"tt.ordernr 'Ordernr'," & _
"PostIt.Text as 'CM', " & _[blue]
"tt.ordernr + ' ---- ' + PostIt.Text as 'NewColumn' " & _[/blue]
"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.

 

[Xsi]

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

 

[Xsi]

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..

 

[softhemc]

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

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

 

[Xsi]

Thank you works as a charm!

 

[gmmastros]

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.aspx?f=255&MSPPError=-2147217396

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

 

[Xsi]

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.