question: I have created a table with NAME and SKILLS. In SKILLS column there are multiple skill set for each individual. As a result what I want is just separate each skill of each individual and place it in another row. I am looking for a LOOP used scrip so there is no matter how much SKILLS there in the first table. First picture is the question and second picture is the result what I want. It am woking in ms sql/ sql server tool. I would be very appreciated if somebody help me on this. Thank you in advance.
create table DBO.SKILL_SET ( NAME VARCHAR(50), SKILLS VARCHAR(500) )
insert into SKILL_SET values ('John','"sql","oracle"') insert into SKILL_SET values ('Michel','"java","C#",".Net"') insert into SKILL_SET values ('Pedro','"ms-word","ms-excel","Access","power point"') insert into SKILL_SET values ('Alex','"sql","oracle","java","C#",".Net"') insert into SKILL_SET values ('Jack','"java","C#",".Net","ms-word","ms-excel","Access","power point"')
SELECT * FROM DBO.SKILL_SET
the result should be like below. Since there are multiple SKILLS it is better if there is a loop script used.
| NAME | SKILLS |
|---|---|
| john | sql |
| john | oracle |
| michel | java |
| michel | c# |
| michel | .net |
| pedro | ms-word |
| pedro | ms-excle |
| pedro | access |
| pedro | power point |
| alex | sql |
| alex | orcle |
| alex | java |
| alex | c# |
| alex | .net |
| jack | java |
| jack | c# |
| jack | .net |
| jack | ms-word |
| jack | ms-excle |
| jack | access |
| jack | power point |