Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Здравствуйте
Третий день ломаю голову над преображением таблицы.
Сейчас объясню на пальцах. Бурят скважину, из нее вытаскивают керн и геолог его описывает. У него программа состоит из четырех вкладок, на каждой вкладке свои "от-до", чтобы знать о каком интервале идет речь.
Сама таблица выглядит так


declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация')
select * from @GEODETAIL

Но в таком виде она не читаема, как мне ее переделать в такую как на картинке? Самое главные чтобы подынтервали совпадали с основным интервалом?
Картинка с другого сайта.

Кажется у меня кривые руки...

К сообщению приложен файл. Размер - 45Kb
31 янв 18, 07:12    [21152986]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Очень прошу вас помочь...Я уже с ума схожу - ничего не получается...
Добрый, Копеллы, Дамирович и другие гуру эскюэля...Я вас очень жду)))
31 янв 18, 07:14    [21152990]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444,

declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация')
select a.*,others.* from @GEODETAIL a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Sec_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Sec_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alt_RockName',
                                   row_number() over (order by geolfrom) as rn_alt
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Alt_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Min_RockName',
                                   row_number() over (order by geolfrom) as rn_min
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Min_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) min
						 on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName'
31 янв 18, 07:52    [21153023]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly, спасибо большое
Эх как бы мне научится такому виртуозному владению

Буду сейчас изучать скрипт, может если пойму, то в будущем и сама смогу
31 янв 18, 08:46    [21153086]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Подскажи пожалуйста, а как избежать дублирование первого столбца, ну чтобы вместо дубликатов тоже NULL был

Kopelly
katish444,

declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация')
select a.*,others.* from @GEODETAIL a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Sec_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Sec_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alt_RockName',
                                   row_number() over (order by geolfrom) as rn_alt
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Alt_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Min_RockName',
                                   row_number() over (order by geolfrom) as rn_min
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Min_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) min
						 on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName'
31 янв 18, 08:54    [21153108]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Добрый Э - Эх
Guest
katish444
как

Kopelly
katish444,

declare @GEODETAIL table (Holeid varchar(20), geolfrom float, geolto float, name varchar(20),value varchar(20))
insert into @GEODETAIL (holeid, geolfrom, geolto, name, value) values
('hr-1',0,8,'Lith_RockName','Песчанник'),
('hr-1',8,50,'Lith_RockName','Алевролит'),
('hr-1',50,100,'Lith_RockName','Известняк'),
('hr-1',100,108,'Lith_RockName','Гравелиты'),
('hr-1',108,200,'Lith_RockName','Алевролиты'),
('hr-1',0,5,'Sec_RockName','Глина'),
('hr-1',30,38,'Alt_RockName','Переслаивание'),
('hr-1',120,180,'Min_RockName','Минерализация'),
('hr-1',190,200,'Min_RockName','Минерализация')
 select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid
      , a.*,others.* from @GEODETAIL a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Sec_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Sec_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alt_RockName',
                                   row_number() over (order by geolfrom) as rn_alt
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Alt_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Min_RockName',
                                   row_number() over (order by geolfrom) as rn_min
                              From @GEODETAIL b 
                             Where a.holeid = b.holeid and b.name = 'Min_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) min
						 on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName'
так
31 янв 18, 09:17    [21153157]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Добрый Э - Эх, все равно дублирует

К сообщению приложен файл. Размер - 28Kb
31 янв 18, 09:42    [21153209]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Добрый Э - Эх,
тьфу, не то вообще приложила

К сообщению приложен файл. Размер - 67Kb
31 янв 18, 09:43    [21153213]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Добрый Э - Эх
Guest
katish444,

так я тебе на примере поля holeid показал общий подход к реализации твоей хотелки в виде нового поля x_holeid.
уж на остальные поля, будь добра, распиши сама... копи-паст + замена имени столбца, думается, посильная для тебя операция?
можно по принципу товарища Kopelly - в outer apply запихать и выводить в том виде, в каком нужно...
31 янв 18, 10:03    [21153302]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
да, спасибо
я поняла. С меня шоколадка))
Добрый Э - Эх
katish444,

так я тебе на примере поля holeid показал общий подход к реализации твоей хотелки в виде нового поля x_holeid.
уж на остальные поля, будь добра, распиши сама... копи-паст + замена имени столбца, думается, посильная для тебя операция?
можно по принципу товарища Kopelly - в outer apply запихать и выводить в том виде, в каком нужно...
31 янв 18, 10:09    [21153319]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Хотя я вам уже не просто шоколадку, а ящик коньяка должна)))
31 янв 18, 10:10    [21153324]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Добрый Э - Эх
katish444,

так я тебе на примере поля holeid показал общий подход к реализации твоей хотелки в виде нового поля x_holeid.
уж на остальные поля, будь добра, распиши сама... копи-паст + замена имени столбца, думается, посильная для тебя операция?
можно по принципу товарища Kopelly - в outer apply запихать и выводить в том виде, в каком нужно...


Я изменила, но теперь наоборот много чего пропало. Подскажи пожалуйста что не так?
select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid,
case row_number() over(partition by a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,
a.GEOLFROM,a.GEOLTO,a.VALUE as Lith_RockName ,geolfrom_sec,geolto,Lith3_RockName,geolfrom_alt,geolto_alt,Alteration,geolfrom_min,geolto_min,Mineralisation from GEODETAILS a
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Lith3_RockName',
row_number() over (order by geolfrom) as rn_sec
From GEODETAILS b
Where a.holeid = b.holeid and b.name = 'Lith3_RockName'
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) Sec
full join
(Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alteration',
row_number() over (order by geolfrom) as rn_alt
From GEODETAILS b
Where a.holeid = b.holeid and b.name = 'Alteration'
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) alt
on rn_sec = rn_alt
full join
(Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Mineralisation',
row_number() over (order by geolfrom) as rn_min
From GEODETAILS b
Where a.holeid = b.holeid and b.name = 'Mineralisation'
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto) min
on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName' and holeid='uzuz-17-002'order by a.GEOLFROM

К сообщению приложен файл. Размер - 110Kb
31 янв 18, 13:53    [21154213]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444,
А если так:

select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid,
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,a.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,
31 янв 18, 13:59    [21154241]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Урра! Все как нужно! Пойду в пляс!
Спасибо огромнейшее


Kopelly
katish444,
А если так:

select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid,
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,a.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,
31 янв 18, 14:01    [21154250]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Теперь в других столбцах дублирует...
Рада я рабовалась
Теперь остальные столбцы засунуть в CASE, но так почему то не срабатывает...

Kopelly
katish444,
А если так:

select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid,
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,a.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,


К сообщению приложен файл. Размер - 141Kb
31 янв 18, 14:14    [21154291]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
вы явно ерундой занимаетесь, вместо этого бы взяли бы Excel и вытащили бы данные с SQL.
там есть специальные средства для анализа, или посмотрите в сторону BI
31 янв 18, 14:17    [21154298]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Программа в которой я работаю понимает только SQL и работать с экселем не умеет
Konst_One
вы явно ерундой занимаетесь, вместо этого бы взяли бы Excel и вытащили бы данные с SQL.
там есть специальные средства для анализа, или посмотрите в сторону BI
31 янв 18, 14:20    [21154317]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444,
Замени во всех (трёх) местах
and a.geolto>b.geolfrom and a.geolfrom<=b.geolto
На
and a.geolto>b.geolfrom and a.geolfrom<b.geolto
31 янв 18, 14:24    [21154335]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Kopelly,
Лишние повторы уйдут, а нужные (которые идут на несколько основных интервалов) - остануться
31 янв 18, 14:26    [21154338]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
К сожалению, не сработало. Все заменила. Сейчас попробую для всех CASE создать

[SUB][SUP]
select case row_number() over(partition by a.holeid order by a.geolfrom) when 1 then a.holeid end as x_holeid, 
case row_number() over(partition by a.holeid,a.geolfrom order by a.geolfrom) when 1 then a.GEOLFROM end as geol_from,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto order by a.geolfrom) when 1 then a.GEOLTO end as geol_to,
case row_number() over(partition by a.holeid,a.geolfrom,a.geolto,a.value order by a.geolfrom) when 1 then a.VALUE end as Lith_RockNameee,



geolfrom_sec,geolto_sec,Lith3_RockName,geolfrom_alt,geolto_alt,Alteration,geolfrom_min,geolto_min,Mineralisation from GEODETAILS a 
outer apply (Select * From (Select geolfrom as geolfrom_sec, geolto as geolto_sec, value as 'Lith3_RockName',
                                   row_number() over (order by geolfrom) as rn_sec 
                              From GEODETAILS b 
                             Where a.holeid = b.holeid and b.name = 'Lith3_RockName' 
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) Sec
					  full join 
					        (Select geolfrom as geolfrom_alt, geolto as geolto_alt, value as 'Alteration',
                                   row_number() over (order by geolfrom) as rn_alt
                              From GEODETAILS b 
                             Where a.holeid = b.holeid and b.name = 'Alteration' 
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) alt
						 on rn_sec = rn_alt
					  full join 
					        (Select geolfrom as geolfrom_min, geolto as geolto_min, value as 'Mineralisation',
                                   row_number() over (order by geolfrom) as rn_min
                              From GEODETAILS b 
                             Where a.holeid = b.holeid and b.name = 'Mineralisation' 
                               and a.geolto>b.geolfrom and a.geolfrom<b.geolto) min on isnull(rn_sec,rn_alt) = rn_min
) others
WHere a.name = 'Lith_RockName'[/SUP][/SUB]


Kopelly
Kopelly,
Лишние повторы уйдут, а нужные (которые идут на несколько основных интервалов) - остануться
31 янв 18, 14:32    [21154368]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
katish444
К сожалению, не сработало. Все заменила. Сейчас попробую для всех CASE создать

Покажи результат.
31 янв 18, 14:34    [21154379]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
всё равно советую в сторону MS BI посмотреть, у вас же чистый OLAP
31 янв 18, 14:35    [21154384]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, пожалуйста с запросом  [new]
katish444
Member

Откуда:
Сообщений: 261
Kopelly
katish444
К сожалению, не сработало. Все заменила. Сейчас попробую для всех CASE создать

Покажи результат.

Я поняла. Минерализация по идее не бьет с основным интервалом, поэтому она попадает в оба интервала
Так что скрипт идеален, это просто особенность данных)) ) Спасибо
2 фев 18, 06:21    [21159401]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить