T-SQL FOR XML: Шпаргалка

Этот пост, прежде всего, написан лично для меня. Описанная ниже задача возникает в моей практике регулярно, но достаточно редко, чтобы я все успел забыть. Чтобы впредь не изобретать каждый раз велосипед, я написал эту шпаргалку.

Итак, даны три таблицы. Список полётных заданий Poletnoe, список лётного состава Crew, и промежуточная таблица, обеспечивающая связь многие-ко-многим conPoletnoeCrew:

Из тестовых данных видно, что у нас в базе два полётных задания: P001 Иванов и Петров, и P002 Иванов и Сидоров:

Задача: вывести список людей для каждого полётного.

Очевидны два тривиальных мейнстримовых решения:
1. Объединить все три таблицы одной квери: SELECT Poletnoe.Number, Crew.Name FROM conPoletnoeCrew INNER JOIN Crew ON conPoletnoeCrew.id_Crew = Crew.id INNER JOIN Poletnoe ON conPoletnoeCrew.id_Poletnoe = Poletnoe.id
2. Объединить квери только Crew и conPoletnoeCrew, организовать цикл по таблице полётных и вложенный цикл по квери.
Первый способ выдает данные в неудобном для обработки виде, и не годится, если мы уже работаем внутри сложной квери. Второй способ может оказаться непозволительно ресурсоемким при достаточно большом количестве записей в таблицах.

Для меня часто возникает необходимость выводить подобные списки как дополнительную справочную информацию, и очень удобно выводить этот список одной строкой. В литературе этот способ мне еще не встречался.

Мой способ:

SELECT Number,(SELECT Name+' ' FROM Crew INNER JOIN conPoletnoeCrew ON Crew.id = conPoletnoeCrew.id_Crew WHERE conPoletnoeCrew.id_Poletnoe=Poletnoe.id ORDER BY Crew.id FOR XML PATH('')) AS Ekipazh FROM Poletnoe
Результат работы запроса:

Вкратце, разберем. В скобочках – вложенная квери, которая собственно и формирует строку с экипажем.
(SELECT Name+' ' – имя члена экипажа и строка-разделитель. Вместо пробела ‘ ‘ можно использовать 'br', или что угодно для удобной обработки получившейся строки.
INNER JOIN conPoletnoeCrew ON Crew.id = conPoletnoeCrew.id_Crew – привязка промежуточной таблицы к таблице Crew
WHERE conPoletnoeCrew.id_Poletnoe=Poletnoe.id – привязка промежуточной таблицы к таблице Polentoe
ORDER BY Crew.id – в каком порядке выводить людей
FOR XML PATH('')) AS Ekipazh – в этом фрагменте и заключается главный фокус. Движок квери готовит данные для вывода в виде XML. Если вместо пустой строки в PATH('') что-то указать, то каждый элемент будет выведен в обрамлении XML-тегов, мне как правило это не нужно. AS Ekipazh – это не только название поля с экипажем, но и указание движку готовить результат в текстовом виде, иначе мы получим XML в бинарном T-SQL представлении.

Этот способ отлично работает в MS SQL Server 2005, и теоретически, должен работать во всех T-SQL СУБД. Если пригодится кому-нибудь еще, я буду только рад :)