unpivot sql table

Question

I have a table with logid,skilllevel,logskill where Data is like

logid, skilllevel1, skilllevel2,skilllevel3,logonskill1,logonskill2,logonskill3,

101,     90,          40,         60           1          2            3

102,      30,         20,         10           4          5            6

I want to get it arranged like the following:

logid,  skilllevel,  logonskill , skillposition

101,      90,            1           1

101,       40,           2           2

102,       30,           4           1

skilllevel1 corresponds to logonskill1 as so on skillposition is the substring of logonskill

How can I achieve this?

Answer 1

My preferred method is a lateral join, using apply:

select v.*
from t cross apply
     (values (logid, skilllevel1, logonskill1, 1),
             (logid, skilllevel2, logonskill2, 2),
             (logid, skilllevel3, logonskill3, 3)
     ) v(logid, skilllevel, logonskill, skillposition)
where skilllevel is not null or logonskill is not null;

Lateral joins are very powerful. This is just one or many things that you can do with apply.