Need help on t-sql 2012

See attach file.
I have the input Table, look up table and try to update @temp4 table base on input data.  I am looking to update result as excel show.  Thank you very much for help!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
change the from to be the C table and you need to use a char index for the temp / temp2 join.

update c
set c.Comp1 = b.sysguid
from @temp4 c
join @temp a on c.PID = a.PId
join @temp2 b on charindex(',' + b.ItemCode + ',', ','+a.ItemCode +',') > 0

Open in new window

jfreeman2010Author Commented:
Hi Kyle - thank you very much for your help.  
the update sql only update the comp1 column only.  
I need to update comp1, comp2, comp3, comp4 and comp5 base on the input column itemcode.   If this column have multiple itemcode, them the match sysguid will need to be update.  see the attached file, last excel table show.
Kyle AbrahamsSenior .Net DeveloperCommented:
can you post your query as a comment so I can work with it?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

jfreeman2010Author Commented:
-- input
 declare @TEMP table (PID int, ItemCode varchar(300))  
-- look up
 declare @TEMP2 table (ID int, ItemCode varchar(100), Description varchar(60), Assortment int, sysguid uniqueidentifier)
 -- output/update base on input
 declare @TEMP4 table (PID int, Comp1 uniqueidentifier, Comp2 uniqueidentifier, Comp3 uniqueidentifier, Comp4 uniqueidentifier, Comp5 uniqueidentifier);

    insert into @temp values(457720, 'IHICAQ13');
      insert into @temp values(1539478, 'IHICAQ7');
      insert into @temp values(825479, 'IHICAQ2,IHICAQ13,IHICAQ5');
      insert into @temp values(2825439, 'IHICAQ4,IHICAQ2,IHICAQ1,IHICAQ5,IHICAQ3');
      insert into @TEMP2 values(356, 'IHICAQ1', 'BP', '28', 'E45F296B-FC74-4BD8-8157-095F76577AFE');
      insert into @TEMP2 values(357, 'IHICAQ2', 'LDL', '28', '181BEE15-83B4-4EED-A758-65E9EBE33220');
      insert into @TEMP2 values(358, 'IHICAQ3', 'Trig', '28', 'C2309516-7D59-4590-B28B-4FD44598F2D0');
      insert into @TEMP2 values(359, 'IHICAQ4', 'Gluc', '28', '7AFB4B9E-AD70-443A-85A8-DECE6C572B1E');
      insert into @TEMP2 values(357, 'IHICAQ5', 'Tobacco', '28', '57DC3423-9676-497D-A77A-0427D7AFC80F');
      insert into @TEMP2 values(358, 'IHICAQ7', 'NotP','28',  '37F7EF15-3DB5-4F30-B9C0-0FCBB544B2C0');
      insert into @TEMP2 values(359, 'IHICAQ13', 'A1c', '28', '2EA2A54F-2237-4339-84E8-4DDEF68A29E9');

      insert into @TEMP4 values(457720, NULL, NULL, NULL, NULL, NULL);
      insert into @TEMP4 values(1539478, NULL, NULL, NULL, NULL, NULL);
      insert into @TEMP4 values(825479, NULL, NULL, NULL, NULL, NULL);
      insert into @TEMP4 values(1577208, NULL, NULL, NULL, NULL, NULL);
      insert into @TEMP4 values(2539474, NULL, NULL, NULL, NULL, NULL);
      insert into @TEMP4 values(2825439, NULL, NULL, NULL, NULL, NULL);
jfreeman2010Author Commented:
I am looking the result like this:
PID      Comp1      Comp2      Comp3      Comp4      Comp5
457720      2EA2A54F-2237-4339-84E8-4DDEF68A29E9      NULL      NULL      NULL      NULL
1539478      37F7EF15-3DB5-4F30-B9C0-0FCBB544B2C0      NULL      NULL      NULL      NULL
825479      181BEE15-83B4-4EED-A758-65E9EBE33220      2EA2A54F-2237-4339-84E8-4DDEF68A29E9      57DC3423-9676-497D-A77A-0427D7AFC80F      NULL      NULL
1577208      NULL      NULL      NULL      NULL      NULL
2539474      NULL      NULL      NULL      NULL      NULL
2825439      181BEE15-83B4-4EED-A758-65E9EBE33220      181BEE15-83B4-4EED-A758-65E9EBE33220      E45F296B-FC74-4BD8-8157-095F76577AFE      57DC3423-9676-497D-A77A-0427D7AFC80F      C2309516-7D59-4590-B28B-4FD44598F2D0
jfreeman2010Author Commented:
Hi Kyle - any luck on this issue ?
Kyle AbrahamsSenior .Net DeveloperCommented:
hi J, sorry have been busy here.  Haven't had a chance to look at it but will get back to you when I can.
jfreeman2010Author Commented:
thanks you Kyle.
jfreeman2010Author Commented:
I found the solution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfreeman2010Author Commented:
I found the solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.