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