hi santosh,
Just run the following script as it is in SQL Server Management Studio.. Later change the script according to your tables
--Sample data of tables: @CV_Upload , @CV_Content and @Skills
DECLARE @CV_Upload TABLE( Id int,CV_Content varchar(max),Primary_Skill varchar(100))
insert into @CV_Upload values(1, 'asdfsad sdiaksaj asoeiw o waejwqoe wo 17 You are designing a new query that will eventually be used by a new software application. It is important that the application testing knows the id value testing of the last row that was inserted. Rather than re-run another query just to get the maximum id value, your friend has told java you to include an inbuilt function. What might that function be 18) Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do?', NULL),
(2,
'You are designing a new table that will hold information about medical records. Another table java called documents contains a foreign key that references this table. To make sure information is cleaned up java when a delete operation is testing performed, you have added an ON DELETE CASCADE statement to the foreign key relationship. What will happen when a row is deleted from the medical records table?' , NULL
)
declare @CV_Content TABLE ( id int, content varchaR(max) )
insert into @CV_Content values(1, 'asdfsad sdiaksaj asoeiw o waejwqoe wo 17 You are designing a new query that will eventually be used by a new software application. It is important that the application testing knows the id value testing of the last row that was inserted. Rather than re-run another query just to get the maximum id value, your friend has told java you to include an inbuilt function. What might that function be 18) Your manager has asked you to check the index stats for a particular table called Address. You have executed the following query (see below) and it reported that the avg_fragmentation_in_percent is 15%. What should you do?'),
(2,
'You are designing a new table that will hold information about medical records. Another table java called documents contains a foreign key that references this table. To make sure information is cleaned up java when a delete operation is testing performed, you have added an ON DELETE CASCADE statement to the foreign key relationship. What will happen when a row is deleted from the medical records table?'
)
declare @Skills table( words varchar(50))
insert @Skills values('net'), ('java'), ('testing')
--Get the number of times a skill repeated for each CV_Upload id
SELECT id, SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)), words,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) DESC) RN
FROM @CV_Content
CROSS APPLY @Skills
GROUP BY id, words
-- Update the CV_Upload table according to maximum times a skill reapeated
UPDATE @CV_UPLOAD
SET Primary_Skill = cvContent.words
FROM @CV_UPLOAD cvUpload
JOIN (SELECT id, SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) NumOfTimes, words,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY SUM((LEN(content) - LEN(REPLACE(content, words, '')))/LEN(words)) DESC) RN
FROM @CV_Content
CROSS APPLY @Skills
GROUP BY id, words
) cvContent ON CVcontent.id = cvUpload.id
WHERE RN=1
--Cross check the result
SELECT * FROM @CV_UPLOAD
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator