how do I populate one table from another and concatenate fields in the process?

We changed the structure of a key table in our database.  Information that used to be stored in multiple columns will now be stored in a single concatenated string field.  I need to write a transact sql statement to populate the new table with historical data from the old table.  

OLD TABLE:
Rec_No as int 4
type1 as smallint
type2 as smallint
type3 as smallint
typename as varchar 100

NEW TABLE:
Rec_No as int 4
Type_String as varchar 4000

Type1, type2, type3 and typename in the OLD TABLE will have to be concatenated into a single string that will be written into Type_String in the NEW TABLE.

There is one catch.  Only nonzero values of type1, type2, and type3 should be added to the string (the type1, type2, and type3 columns store quantity).  Its a bit more complicated than that, but I think this simplification is enough to workout the part I dont understand.  I think this example in comma delimited format pretty much says it all:

OLD TABLE:
"2234", "2", "3", "0", "Gloss"
"2235", "0", "0", "1", "Plain"
"2236", "4", "4", "8", "Plain"


NEW TABLE
"2234", "Type1-Gloss-2;Type2-Gloss-3"
"2235", "Type3-Plain-1"
"2236", "Type1-Plain-4;Type2-Plain-4;Type3-Plain-8"

Thanks for your expertise and time!

-meuedyn







meuedynAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
MNelson831Connect With a Mentor Commented:
grr... not quite right.. sorry:

INSERT INTO
     NewTable(Rec_No, Type_String)
SELECT
     Rec_No,
     (
         Case
              When isnull(type1,0) <> 0 then 'Type1-' + Type1 + '-' + TypeName + ';'
              Else ''
         End    +
         Case
              When isnull(type2,0) <> 0 then 'Type2-' + Type2 + '-' + TypeName + ';'
              Else ''
         End    +
         Case
              When isnull(type3,0) <> 0 then 'Type3-' + Type3 + '-' + TypeName + ';'
              Else ''
         End
     ) as Type_string
FROM
     OldTable
0
 
SireesCommented:
Can you try this

INSERT INTO NewTable(Rec_No, Type_String)
SELECT Rec_No, (type1+'-'+type2+'-'+type3) as Type_string
FROM OldTable
0
 
meuedynAuthor Commented:
I think I need some sort of  if...then statement to check to see if the values in type1, type2, and type3 are zero or not.  This statement seems like it would just concatenate them all, no? or did I miss something?  Also, the typename has to part of the string too in the format described!
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Learn more
 
MNelson831Commented:
>>There is one catch.  Only nonzero values of type1, type2, and type3 should be added to the string

Also, nulls will kill the whole string if any exists and are added. So to replace nulls and zeros with empty string:

INSERT INTO
     NewTable(Rec_No, Type_String)
SELECT
     Rec_No,
     (
          replace(isnull(type1,''),0,'') + ';' + replace(isnull(type2,''),0,'') + ';' + replace(isnull(type3,''),0,'')
     ) as Type_string
FROM
     OldTable
0
 
MNelson831Commented:
>>>  Also, the typename has to part of the string too in the format described!

oops I missed that:

INSERT INTO
     NewTable(Rec_No, Type_String)
SELECT
     Rec_No,
     (replace(
         Case
              When isnull(type1,0) <> 0 then 'Type1-' + Type1 + '-' + TypeName + '-'
              Else ''
         End   + ';'  +
         Case
              When isnull(type2,0) <> 0 then 'Type2-' + Type2 + '-' + TypeName + '-'
              Else ''
         End   + ';'  +
         Case
              When isnull(type3,0) <> 0 then 'Type3-' + Type3 + '-' + TypeName
              Else ''
         End
     ,'--','-') as Type_string
FROM
     OldTable
0
 
meuedynAuthor Commented:
thanks MNelson, did my import and it worked great.:)  I took out the isnull, btw, couldnt make it work, but its fine,.. I verified it in another way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.