param trivedi param trivedi - 7 months ago 8
SQL Question

How to Join two table using pivot with combining two field in 1 table

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.

Answer
declare @job_skill table (id_skill int,min_job int ,ideal_job int ,max_job int)
insert into @job_skill
VAlues
(         1 ,       0 ,          0 ,      1),
(         2 ,       0 ,          1 ,      1),
(         7 ,       1 ,          1 ,      1),
(         8 ,       1 ,          1 ,      1)


declare @Job_Education table (Degree_Job int,  Field_Job int,  Min_Job int,   Ideal_Job int,  Max_Job int)
insert into @job_education
values
(              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 
*/

select *
from
(
select 1 as seq,'Min' as Criteria,cast(id_skill as char(10)) as COL, min_job jobval
from   @job_skill
union  
select 2,'Ideal',cast(id_skill as char(10)) as criteria,ideal_job 
from   @job_skill
union  
select 3,'Max',cast(id_skill as char(10)) as criteria, Max_job
from   @job_skill
union  
select 1, 'Min','(' + rtrim(cast(degree_job as char(4))) +  ' ' + rtrim(cast(field_job as char(4))) +')'  , min_job 
from    @job_education
union  
select 2, 'Ideal','(' + rtrim(cast(degree_job as char(4))) +  ' ' + rtrim(cast(field_job as char(4))) +')'  , ideal_job
from    @job_education
union  
select 3, 'Max','(' + rtrim(cast(degree_job as char(4))) +  ' ' + rtrim(cast(field_job as char(4))) +')'   , Max_job
from    @job_education
) s
pivot  (max(jobval) for col in([1],[2],[7],[8],[(7 37)],[(7 106)],[(13 37)],[(13 106)])) pvt
order by seq
Comments