select j.jobid, j.mtlid, max(case when fname='lunit' then fvalue else '' end) as lunit, max(case when fname='wunit' then fvalue else '' end) as wunit from j join m on j.mtlid=m.mtlid group by j.jobid,j.mtlid
select JobID,Mtlid,lunit,wunit from( select a.JobID,a.Mtlid ,max(case when Fname='lunit' then unitname else '' end) as lunit ,max(case when Fname='wunit' then unitname else '' end) as wunit from J as a inner join ( select Mtlid,Fname,unitname from M as b left outer join N as d on b.Fvalue=d.unitid ) as c on a.Mtlid=c.Mtlid group by a.JobID,a.Mtlid ) as e
unitid unitname
0019 公斤
0018 米
0030 分米
那要得到
JobID Mtlid lunit wunit
dpn001 mt001 公斤 米
dpn002 mt002 分米 分米
又該如何呢?
from(
select a.JobID,a.Mtlid
,max(case when Fname='lunit' then unitname else '' end) as lunit
,max(case when Fname='wunit' then unitname else '' end) as wunit
from J as a
inner join (
select Mtlid,Fname,unitname
from M as b
left outer join N as d
on b.Fvalue=d.unitid
) as c
on a.Mtlid=c.Mtlid
group by a.JobID,a.Mtlid
) as e