|
Уважаемые форумчани. Пытаюсь сделать сводную таблицу, но у меня не выходит. Есть таблицы с данными
create table HOLECOORD (holeid varchar(255), projectcodevarchar(255), coordinatesetvarchar(255), x int,y int,z int)
create table HOLEDETAILS (holeid varchar(255), projectcode varchar(255), name varchar(255), value varchar(255))
create table DRILLHOLEDIAMDETAILS (holeid varchar(255), projectcode varchar(255), name varchar(255), value varchar(255))
create table HOLESURVEY (holeid varchar(255), projectcode varchar(255), depth int, azimuth decimal(20,5), dip decimal(20,5), azimuthGridName varchar(255))
create table DRILLINGDETAIL (drillingdate varchar(255), shift varchar(255), holeid varchar(255), projectcode varchar(255), name varchar(255), value varchar(255))
insert into HOLECOORD (holeid, projectcode, coordinateset, x,y,z) values ('UZSE-17-001', 'UZ', 'WGS84_Zone_43N_Plan', 100,200,300)
insert into HOLEDETAILS (holeid, projectcode, name, value) values ('UZSE-17-001', 'UZ', 'Drill_Date_Start', '26-oct-2017')
insert into HOLEDETAILS (holeid, projectcode, name, value) values ('UZSE-17-001', 'UZ', 'Depth_Project', '100')
insert into DRILLHOLEDIAMDETAILS (holeid, projectcode, name, value) values ('UZSE-17-001', 'UZ', 'DrillRigModel', 'LF90')
insert into HOLESURVEY (holeid, projectcode, depth, azimuth, dip, azimuthGridName) values ('UZSE-17-001', 'UZ',0,20,90,'AZ')
insert into HOLESURVEY (holeid, projectcode, depth, azimuth, dip, azimuthGridName) values ('UZSE-17-001', 'UZ',20,40,60,'AZ')
insert into DRILLINGDETAIL (drillingdate, shift, holeid, projectcode, name, value) values ('27-oct-2017','day','UZSE-17-001',
'UZ',DrillDepth','20')
Необходимо создать запрос, у меня не отображает Azimuth Dip
select distinct h.holeID, hcd.X, hcd.Y,s.AZIMUTH, s.DIP, h.projectcode,dd.DRILLINGDATE,
max(case when dr.name = 'DrillRigModel' then dr.Value end) as DrillRigModel,
max(case when hd.name = 'Depth_Project' then hd.Value END) as Depth_Project,
max(case when hd.name = 'Drill_Date_Start' then hd.Value END)as Drill_Date_Start
from HOLEDETAILS hd left join
DRILLHOLEDIAMDETAILS dr on dr.HOLEID=hd.HOLEID left join holelocation h on h.HOLEID=hd.HOLEID
left join ( select distinct hs1.Holeid, hs1.Depth, hs1.AZIMUTH, hs1.DIP from HOLESURVEY hs1 inner join
(select distinct hs2.Holeid, min(hs2.Depth)D from HOLESURVEY hs2 group by hs2.Holeid)hs on hs.Holeid=hs1.Holeid and hs.D=hs1.Depth group by hs.Holeid, hs1.Holeid, hs1.Depth, hs1.AZIMUTH, hs1.DIP)s on h.HOLEID=s.HOLEID
left join DRILLINGDETAIL dd on dd.holeid=hd.HOLEID
left join (select holeid, projectcode, round(x,2)X ,round(y,2)Y,round(z,2)Z
from HOLECOORD where coordinateset = 'WGS84_Zone_43N_Plan')hcd on hcd.holeid=h.holeid and h.projectcode=h.projectcode
where h.projectcode = 'uz' and DRILLINGDATE = '2ct-2017'
GROUP BY
h.holeID, h.projectcode, hcd.X, hcd.Y,s.AZIMUTH, s.DIP,dd.DRILLINGDATE
Таблица должна получится как на картинке, но без Null
Помогите пожалуйста
[img=]
|
