Update column using comma-separated values in SQL Server

Question

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

You shouldn't be storing the values like that. Is fixing (i.e. properly normalize it) your data model an option? — user330315
– user330315, Commented Oct 25, 2013 at 12:25
No... This is an old Table. I wont b able to alter this table becs it is already in use :( — san
– san, Commented Oct 25, 2013 at 12:30
I need it continually. The records will be added by a scheduled service which i does not have access. I need to update all the records where SkillDesc is null — san
– san, Commented Oct 25, 2013 at 13:03

user2919277 · Accepted Answer · 2013-10-25 12:45:25Z

1

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

answered Oct 25, 2013 at 12:45

user2919277

2462 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

san Over a year ago

The values are added by a job which i does not have access. The table is containing more data and i need to do the update for all the records... It s taking much time when i used cursor

user2919277 Over a year ago

This would be more work and a pain.. wast of lot of time. But still u have a solution for this. extract the skillset column to the tmp tbale. and join the table-2 with it where u get the desc. split each column and map the desc with CASE statement and concatenate them with comma. I know it s too confusing.. wel If you take it step by step u can do it..

Ranjitha · Accepted Answer · 2013-10-25 12:49:54Z

1

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

answered Oct 25, 2013 at 12:49

Ranjitha

836 bronze badges

3 Comments

san Over a year ago

Ya... But i cant change that.... the table is old and which hv more data and already in use :(

Ranjitha Over a year ago

only way is to use a temporery table for this. Use the temp table split the , seperated and add corresponding desc for each , by using case statement .. put them all in one one tmp table.. Then using this tmp table add the results to the main tbale based on ID.

san Over a year ago

Thanks Ranjitha ... i m trying with temp tables

san · Accepted Answer · 2013-10-28 06:03:28Z

0

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

answered Oct 28, 2013 at 6:03

san

1,8594 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

Update column using comma-separated values in SQL Server

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related