I have two table 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
I want to display like this
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 in pivot. If any other method is thee please suggest.
this is the answer. I have the desired Result i want. The query 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