Join two tables using pivot combining two columns in one table

Question

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.

Answer 1

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".

Answer 2

This Query is the answer to my question. It is dynamic.

 Declare @Skill  NVARCHAR(MAX),
        @Degree_Field  NVARCHAR(MAX),
        @Experience  NVARCHAR(MAX),
        @query  NVARCHAR(MAX)


    select @Skill = 
                STUFF((
                        select SEQ
                        From
                        (
                            SELECT  SEQ = (',' + QUOTENAME(Skill_Name) )
                            From
                            (
                            select Skill_Meta.Skill_Name,Min_Job, Ideal_Job, Max_Job
                            FROM Job_Skill
                            Inner Join Skill_Meta On Skill_Meta.ID_SKL = Job_Skill.ID_SKL_Job
                            )SKL

                            UNPIVOT (Val FOR Col IN (Min_Job, Ideal_Job, Max_Job)) AS U
                        ) SKL

                        GROUP By SEQ 
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'') 

    select @Degree_Field = 
                STUFF((
                        select SEQ
                        From
                        (
                            SELECT  SEQ = (',' + QUOTENAME(cast(CONCAT(Degree_Job,' ', Field_Job) as varchar(10))) )
                            FROM Job_Education
                            UNPIVOT (Val FOR Col IN (Min_Job, Ideal_Job, Max_Job)) AS U
                        ) SKL

                        GROUP By SEQ 
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'') 
   select @Experience = 
                STUFF((
                        select SEQ
                        From
                        (
                            SELECT  SEQ = (',' + QUOTENAME(cast(Experience_Job as varchar(10))) )
                            FROM Job_Experience
                            UNPIVOT (Val FOR Col IN (Min_Job, Ideal_Job, Max_Job)) AS U
                        ) SKL

                        GROUP By SEQ 
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'')

    set @query = 
            ';With
            P1_Src As
            (
                SELECT ID_Job,Skill_Name, Val, Col
                FROM Job_Skill
                Inner Join Skill_Meta On Skill_Meta.ID_SKL = Job_Skill.ID_SKL_JOB
                UNPIVOT (Val FOR Col IN (Min_Job, Ideal_Job, Max_Job)) AS U

            ), 
            P1 AS
            (
                SELECT ID_Job,Col,'+@Skill+'
                FROM P1_Src
                PIVOT (MAX(Val) FOR Skill_Name IN ('+@Skill+')) AS Pa
            ),

            P2_Src As
            (
                SELECT ID_Job,CONCAT(Degree_Job,'' '', Field_Job) as DegreeField, Val, Col
                FROM Job_Education
                UNPIVOT (Val FOR Col IN (Min_Job, Ideal_Job, Max_Job)) AS U
            ),

            P2 AS
            (
               SELECT ID_Job,Col,'+@Degree_Field+'
               FROM P2_Src
               PIVOT (MAX(Val) FOR DegreeField IN ('+@Degree_Field+')) AS Pb
            ),

            P3_Src As
            (
                SELECT ID_Job,Experience_Job, Val, Col
                FROM Job_Experience
                UNPIVOT (Val FOR Col IN (Min_Job, Ideal_Job, Max_Job)) AS U
            ),

            P3 AS
            (
               SELECT ID_Job,Col,'+@Experience+'
               FROM P3_Src
               PIVOT (MAX(Val) FOR Experience_Job IN ('+@Experience+')) AS Pc
            )



            select P1.ID_Job,P1.Col,P1.'+@Skill+',P2.'+@Degree_Field+',P3.'+@Experience+' 
            From P1
            Inner Join P2 On P1.ID_Job = P2.ID_Job and P1.Col = P2.Col
            Inner Join P3 On P1.ID_Job = P3.ID_Job and P1.Col = P3.Col'

exec sp_executesql @query