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!
testDate.GIF
jfreeman2010Asked:
Who is Participating?
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.

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

0
 
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.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
can you post your query as a comment so I can work with it?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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);
0
 
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
0
 
jfreeman2010Author Commented:
Hi Kyle - any luck on this issue ?
0
 
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.
0
 
jfreeman2010Author Commented:
thanks you Kyle.
0
 
jfreeman2010Author Commented:
I found the solution.
0

Experts Exchange Solution brought to you by ConnectWise

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