1

Topic: To connect 5 tables on a key and with distinct

Respected . I try to make the pivot table, but at me does not quit. There are tables dataful
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 ')
It is necessary to create request, at me does not display 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
The table should it turns out as on a picture, but without Null
Help please
[img =]

2

Re: To connect 5 tables on a key and with distinct

katish444 wrote:

Respected . I try to make the pivot table, but at me does not quit. There are tables dataful
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))
DRILLHOLEDIAMDETAILS dr on dr. HOLEID=hd. HOLEID left join holelocation h on h. HOLEID=hd. HOLEID
[img =]

What in the table holelocation?

3

Re: To connect 5 tables on a key and with distinct

katish444;
1. All your problems of that the code is written carelessly - yes it is banal to read inconveniently.
2. DISTINCT + GROUP BY is as a condom on a candle.
3. Words describe that is necessary.
4. Add the data. Your request about the test data gives not the same that on a picture.
5. Telepathy

SELECT
hc. [holeid];
hc. [projectcode];
[x] = ROUND (hc. [x], 2);
[y] = ROUND (hc. [y], 2);
[z] = ROUND (hc. [z], 2);
ddd. [drillingdate];
[drill_depth] = ddd. [value];
[drill_rig_model] = drm. [value];
[depth_project] = dp. [value];
[drill_date_start] = dds. [value];
hs. [azimuth];
hs. [dip]
from
HOLECOORD hc
INNER JOIN DRILLINGDETAIL ddd ON (
ddd. [holeid] = hc. [holeid]
AND ddd. [name] = ' DrillDepth ')
LEFT JOIN HOLESURVEY hs ON (
hs. [holeid] = hc. [holeid]
AND hs. [depth] = CONVERT (INT, ddd. [value]))
LEFT JOIN DRILLHOLEDIAMDETAILS drm ON (
drm. [holeid] = hc. [holeid]
AND drm. [name] = ' DrillRigModel ')
LEFT JOIN HOLEDETAILS dp ON (
dp. [holeid] = hc. [holeid]
AND dp. [name] = ' Depth_Project ')
LEFT JOIN HOLEDETAILS dds ON (
dds. [holeid] = hc. [holeid]
AND dds. [name] = ' Drill_Date_Start ')
WHERE
hc. [projectcode] = ' UZ'

4

Re: To connect 5 tables on a key and with distinct

To accelerate answer obtaining in a forum it is necessary to create two identical subjects with a few  a title
http://www.sql.ru/forum/1282930/ne-vyvo … znacheniya

5

Re: To connect 5 tables on a key and with distinct

Rolg Hupin;
Check, them it is far not two.... The history about holes and their drilling any more here develops the first week...

6

Re: To connect 5 tables on a key and with distinct

  - Eh wrote:

Rolg Hupin;
Check, them it is far not two.... The history about holes and their drilling any more here develops the first week...

Not holes, and slits... Also do not develop, and go deep smile

7

Re: To connect 5 tables on a key and with distinct

Ruslan Damirovich;
holes... If I correctly understand major importance of an English word "HOLE"
Slits, in geological sense, it, probably, "WELL" should be...

8

Re: To connect 5 tables on a key and with distinct

  - Eh wrote:

Ruslan Damirovich;
holes... If I correctly understand major importance of an English word "HOLE"
Slits, in geological sense, it, probably, "WELL" should be...

[quote =] Before to swing gold from OIL WELL, it is necessary to find at first OIL DEPOSIT, and then DRILL A HOLE TO THE DEPOSIT.
As my teacher of work spoke, showing on a hole in preparation - "the Hole - in a bum, and it is a hole". In sense there is a term "slit" concerning drilling - it and it is necessary to apply. And "well" is already when a slit drilled and strengthened walls.

But we leave in a rigid offtopic smile