Перекрестный запрос

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

Приведем пример, когда может возникнуть необходимость преобразовать запрос на выборку с группировкой в перекрестный запрос, для рассматриваемой базы данных. В качестве заголовков строк могут использоваться коды поставщиков, а в качестве заголовков столбцов – коды товаров, а на пересечении строк и столбцов – суммы поставок от данного поставщика по данному товару.

В любом перекрестном запросе логически можно выделить:

поле или поля, образующие заголовки строк;

поле, используемое в качестве заголовков столбцов;

выражение, обязательно содержащее агрегатную функцию, которое используется для заполнения клеток таблицы.

Как правило, перекрестный запрос составляется на основе данных из нескольких таблиц.

В схематическом виде запрос на выборку с группировкой можно представить в следуем виде:

Select КодПост, КодТов, Sum(КолТов*Цена) as СумПост

From < связка таблиц >

Where <условие отбора записей>

Group by КодПост, КодТов

Преобразование запроса на выборку с группировкой в перекрестный запрос производится в три этапа:

В запрос включаются две дополнительные команды Transform и Pivot. Команда Transform используется для обозначения того, что запрос будет являться перекрестным, а команда Pivot используется для группировки по столбцам.

Список полей запроса на выборку с группировкой обязательно содержит одно вычисляемое поле, использующее агрегатную функцию. Поле с агрегатной функцией исключается из предложения Select и включается в предложение Transform.

Далее определяется, какое поле будет соответствовать заголовкам строк, а какое - заголовкам столбцов. При определении того, какое поле будет являться заголовками столбцов, проверяются два условия:

входит ли поле в предложение Select,

входит ли поле в состав полей с группировкой.

Поле, удовлетворяющее этим условиям, исключается из предложений Select и Group by и переносится в предложение Pivot.

Преобразуем запрос на выборку с группировкой в перекрестный запрос:

Transform Sum (КолТов*Цена) as СумПост

Select КодПост

From < связка таблиц >

Where <условие отбора записей>

Group by КодПост

Pivot КодТов