Рекурсивные (Иерархические) запросы

Дерево в БД можно представить достаточно просто:

ID | PID | NAME| VALUE

Куда сложней из такой таблицы вытаскивать данные. Можно это делать рекурсивно выполняя маленькие запросы, узнающие значение и родителя.
Оказывается эту задачу можно решить одним запросом используя лишь один T-SQL.
Рассмотрим на примере: необходимо выбрать все ветки/листья элемента, вывести значения элементов и построить полный путь до каждого.
Для этого можно использовать следующий запрос:

WITH tree ( data , id , pid , level , pathstr ) 
AS ( SELECT t.NAME, t.ID, t.PID, 0, CAST ( t.VALUE AS VARCHAR ( 4000 ) ) 
FROM table t
WHERE t.ID = 1 
UNION ALL 
SELECT t.NAME, t.ID, t.PID, t.level + 1 , CAST ( tr.pathstr + '\' + t.VALUE) AS VARCHAR ( 4000 ) ) 
FROM table t
INNER JOIN tree tr 
ON ( t.PID = tr.id ) 
) 
SELECT tr.* 
FROM tree tr 
ORDER BY tr.level, tr.pathstr 

Что делает каждая его часть:

WITH tree ( <список столбцов для выборки из дерева > ) 
AS ( 
<Начальное условие, откуда начнется рекурсия> 
Изначально путь = NAME корня, а уровень задан в 0
Строку с путем кастим к максимальному VARCHAR, т.к. путь может быть очень длинным.
UNION ALL 
<связка текущего запроса с деревом (родитель с идентификатором)>
Тут же мы увеличиваем уровень вложенности, и формируем следующий элемент пути
) 
<Тут мы работаем с полученными значениями, как с обычной таблицей>

Результат будет примерно такой:

id | pid | level | data                | pathstr
1  |  0  |   0   | Значение Корня      | Корень 
2  |  1  |   1   | Значение уровня 1   | Корень / Уровень 1
3  |  1  |   1   | Значение уровня 1.1 | Корень / Уровень 1.1
4  |  2  |   2   | Значение уровня 2   | Корень / Уровень 1 / Уровень 2

Вывод результата выборки в строчку: FOR XML PATH

Бывает необходимость вывести результат выборки в строчку.
Рассмотрим на примере. Есть задача и список исполнителей, необходимо построчно вывести задачи и ответственных. В случае группировки по задаче, ответственному мы получим число записей = число задач * число ответственных, нам же требуется вывести задачи построчно. Это отлично реализуется с помощью инструкции MS SQL - FOR XML PATH, изначально используемой для формирования xml строки из выборки.

Пример кода: task - Задачи, executer - Исполнитель:

SELECT
e.name, -- название задачи
(SELECT t.name + ', ' -- список исполнителей для задачи через запятую
 FROM task t
 WHERE t.executor = e.id
 FOR XML PATH(''))
FROM executer e

Рассмотрим более интересный пример, более близкий к изначальному применению FOR XML PATH - сформируем CSV файл из таблицы одним запросом без всяких циклов:

SELECT
t.field1 + ';' + --объединяем список столбцов через разделитель
t.field2 + ';' +
t.field3 + ';' +
t.field4 + ';
' --перенос строки
FROM table t WHERE 1=1 FOR XML PATH('')

Если field не строка, то его нужно привести: CAST(field AS VARCHAR(50)).

Добавление для ORACLE

SELECT t.executor, wmsys.wm_concat(t.name) 
FROM FROM task t 
GROUP BY t.executor