Join two tables using pivot combining two columns in one table
You can UNPIVOT and then PIVOT.

Half of it is below (untested):

WITH P1_Src As
(
SELECT ID_Skill, Val, Col
FROM Job_Skill
UNPIVOT (Val FOR Col IN (Min_Job, Idea_Job, Max_Job)) AS U
), P1 AS
(
SELECT Col, [1], [2], [7], [8]
FROM P1_Src
PIVOT (MAX(Val) FOR ID_Skill IN ([1], [2], [7], [8])) AS P
)
SELECT *
FROM P1


You would need to apply a case expression to get the desired Criteria string, then extend the example to do the same for the other table and join the two results on Criteria.

To make this dynamic, you would need to look at the places in the query that currently have hard-coded values, extract the distinct values from the table instead, and use these instead of the hardcoded values.

For an example of the basic technique, see Dynamic pivot: sum of sales per month or search for "dynamic pivot".