Чтобы получить корневую строку, просто с помощью функции SUB-STR извлеките подстроку первого значения ENAME в PATH:

select ename,

substr(root,1,instr(root root from ( select ename,

ltrim(sys connect by path(ename ) ) root from emp start with mgr is null connect by prior empno=mgr )

Последний шаг - отметить флагом строку, в поле ROOT которой содержится значение NULL; это и есть корневая строка.

Всякая всячина

В данной главе собраны запросы, по тем или иным причинам не вошедшие в остальные части книги: или потому что глава, в которую они могли бы быть включены, и так слишком обширна, или потому что они представляют собой скорее просто красивые технические решения, чем решения практических задач. Это глава для забавы , ее рецептами вы можете никогда в жизни не воспользоваться. Тем не менее мне они показались интересными, и я решил включить их в книгу.

Создание отчетов с перекрестными ссылками с помощью оператора SQL Server PIVOT

Задача

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

DEPT 10 DEPT 20 DEPT 30 DEPT 40

Решение

Чтобы создать требуемое результирующее множество, не прибегая к выражениям CASE или дополнительным объединениям, используйте оператор PIVOT:

1 select [10] as dept 10,

2 [20] as dept 20,

3 [30] as dept 30,

4 [40] as dept 40

5 from (select deptno, empno from emp) driver

6 pivot (

7 count(driver.empno)

8 for driver.deptno in ( [10],[20],[30],[40] )

9 ) as empPivot

Обсуждение

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

select sum(case deptno when 10 then 1 else 0 end) as dept 10,

sum(case deptno when 20 then 1 else 0 end) as dept 20,

sum(case deptno when 30 then 1 else 0 end) as dept 30,

sum(case deptno when 40 then 1 else 0 end) as dept 40 from emp

DEPT 10 DEPT 20 DEPT 30 DEPT 40

3 5 6 0

Теперь, зная, что происходит по существу, разложим действия оператора PIVOT на составляющие. В строке 5 решения показан вложенный запрос DRIVER:

from (select deptno, empno from emp) driver

Псевдоним driver выбран потому, что строки, возвращаемые этим вложенным запросом (или табличным выражением), напрямую поступают в операцию PIVOT. Оператор PIVOT поворачивает и превращает строки в столбцы, обрабатывая элементы, перечисленные в строке 8 в списке FOR (показан ниже):

for driver.deptno in ( [10],[20],[30],[40] )

Обработка происходит примерно следующим образом:

1. Выполняем операцию агрегации COUNT(DRIVER.EMPNO) для

строк, значение поля DEPTNO которых равно 10.

2. Повторяем то же самое для строк с DEPTNO, равными 20, 30 и 40.

Элементы, перечисленные в квадратных скобках в строке 8, не только определяют значения, участвующие в агрегации, они также используются как имена столбцов результирующего множества (без скобок). Ссылка на элементы списка FOR и присвоение им псевдонимов осуществляется в операторе SELECT решения. Если псевдонимы не заданы, в списке FOR используются имена столбцов без квадратных скобок.

Довольно любопытно, что, поскольку DRIVER - это просто вложенный запрос, его можно сделать более сложным. Например, рассмотрим ситуацию, когда требуется изменить результирующее множество