asked
Case Statement
Hello, I have two tables and I would like to create table 3 with all records from table 1 and a field from table 2. In table 1, I have the following columns (Number, item, year and amount). In table 2 I have (number, owner). +------------------------------------------------------------------------+ | Number Item Year Amount Number Owner | +------------------------------------------------------------------------+ | 123 Corn 2001 200 456 Cute farms | | 345 apple 2000 105 789 PT farms | | 567 orange 2001 50 123 KP farms | | 456 corn 2000 220 345 Jelly farms | +------------------------------------------------------------------------+ I want to create a table with all columns from table 1 with an "Owner" column from table 2 populated with data where item = corn. Hope this make sense Thanks
If I understand it correctly, something like this would work: SELECT t1.Number, t1.Item, t1.Year, t1.Amount, CASE t1.item WHEN 'corn' THEN T2.Owner ELSE NULL END as 'Owner' INTO dbo.table3 FROM dbo.table1 t1 LEFT JOIN dbo.table2 t2 on t1.number = t2.number WHERE t1.item = 'corn' GO EDIT: I've updated the code to reflect a CASE statement that should work. Note that I haven't tested it. This is assuming that table3 doesn't yet exist. Hope that helps!
18 People are following this question.
