Join two tables using pivot combining two columns in one table I have two tables in this format:

Job_Skill
ID_Skill  Min_Job  Idea_Job   Max_Job
=====================================
1        0           0       1
2        0           1       1
7        1           1       1
8        1           1       1

Job_Education
Degree_Job  Field_Job  Min_Job   Idea_Job   Max_Job
===================================================
7        37         0         0         1
7        106        0         1         1
13       37         1         1         1
13       106        1         1         1

Desired Result
Criteria   1  2  7  8 [ 7  37 ] [ 7 106 ]  [13 37]   [13 106]
=============================================================
Min        0  0  1  1    1         1         0          0   
Ideal      0  1  1  1    0         1         1          1
Max        1  1  1  1    1         1         1          1 

How can I achieve this using a pivot, or any other method?
I am using SQL Server 2014. The columns are not fixed; they are dynamic. A static version is also acceptable, as I just want to understand how I should approach this.
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".