CODEWITHSUNDEEP
sql-server
san
san

Reputation: 1859

Update column using comma-separated values in SQL Server

I have 2 tables in SQL Server:

Table1

ID  SkillSetRequired                 SkillDesc
1   100-1,101-1,102-2,103-3          null
2   100-4,105-2                      null
3   100-1,102-2,104-2                null  
4   100-5,102-2                      null   
5   105-1                            null

Table2

ID    SkillName
100   .Net
101   Java
102   JQuery
103   Sql
104   PHP
105   C

I need to update the SkillDesc column of Table1 with the SkillNames. For example in first row I need to update skilldesc as '.Net,Java,Jquery,SQL'

How can I do this with out using cursors?

I am getting the result in a string from the below query for a single record. But I need to update all the rows.

declare @result varchar(max)

SET @result = ''            
SELECT @result = @result + ltrim(rtrim(SkillName)) + ',' from  #Table2 where id in(
                         select SUBSTRING(items, 0 + 1, CHARINDEX('-', items, 1) - 0 - 1)  from split('100-1,101-1,102-2,103-3',','))
                         select @result

Upvotes: 0

Views: 2608

Answers (3)

san
san

Reputation: 1859

I think cursor is the best option even though it is taking time to execute.

     SELECT ID,SkillSetRequired 
     FROM #Table1
     OPEN @CurSkillUpdate
     FETCH NEXT
     FROM @CurSkillUpdate INTO @id,@skills
     WHILE @@FETCH_STATUS = 0
     BEGIN
        SET @result = ''            
        SELECT @result = @result + ltrim(rtrim(SkillName)) + ',' from  #Table2 where id in(
        select SUBSTRING(items, 0 + 1, CHARINDEX('-', items, 1) - 0 - 1)  from split(@skills,','))          
        update #Table1 set SkillDesc=@result where ID=   @id
     FETCH NEXT
     FROM @CurSkillUpdate INTO  @id,@skills
     END

Upvotes: 0

Ranjitha
Ranjitha

Reputation: 83

How did u manage adding 100-1,101-1,102-2,103-3 . This is not the proper way to add...

Upvotes: 1

user2919277
user2919277

Reputation: 246

Firstly, How did u insert 100-1,101-1,102-2,103-3 into the table ?

Do the below maping while adding the above. Use a Case statement like

Case 
when @Skillset = '100' THEN @desc = .Net
when @Skillset = '101' THEN @desc = Java
....
END

same way for other skill sets.

Then while inserting into the table for every id add the skillset and use the @Desc for the which updates

Upvotes: 1

Related Questions