New to SQL, need a little help

• Mike Bowen

  Valued Member

  Points: 69

  More actions

  October 5, 2005 at 2:42 pm

  #87963

  Hey all, I'm a little new to SQL so I'm still learning alot. Right now I'm trying to just take some data from a table, manipulate it and either stick it into another table, or if i can, write the new data directly to a file. OK, here I go.

  I have a table with several columns, and I need to take the data from 2 of the columns and manipulate it by adding some brackets and other characters around the data. For instance...

  Columns: truck, bt

  The data in the truck column are just numbers:

  100, 101, 102.....

  The data in the bt column is just a string:

  xxxxxxx, yyyyyyy....

  What I need is to take each row in the table with the data and create a new table with 1 column and each row would look like this:

  <T100>xxxxxxx</T100>

  <T101>yyyyyyy</T101>...

  But it would be the data from the table, not just fixed values.

  Hope this makes sense, any help would be great!

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  October 5, 2005 at 4:05 pm

  #595293

  Use the + symbol to concatenate fields together.

  caution you need to explicitly cast all your columns to a varchar datatype if they are not already a char.

  create table mytable(pk int identity, col1 int, col2 varchar(10))

  insert into mytable (col1, col2)

  Select 101, 'xxxxx' union

  Select 102, 'yyyyy' union

  Select 103, 'zzzzz' union

  Select 104, 'aaaaa'

  select '<T' + ltrim(str(col1)) + '>' + col2 + '</T' + ltrim(str(col1)) + '>'

  from myTable

  Results

  <T101>xxxxx</T101>

  <T102>yyyyy</T102>

  <T103>zzzzz</T103>

  <T104>aaaaa</T104>