Перекрестные запросы SQL или кросс табличные выражения

Продолжаем осваивать язык запросов SQL и сегодня мы с Вами займемся изучением так называемых перекрестных запросов или их также называют кросс табличные выражения. Иногда можно встретить такое название как транспонирование таблицы (например, в Excel), но смысл во всех этих названиях один и тот же и сейчас в нем мы будем разбираться.

Для начала напомню, что это у нас уже третья статья по SQL. В предыдущих статьях мы рассмотрели:

• Основы языка запросов SQL – оператор SELECT;
• Строковые функции SQL – примеры использования.

А сегодня, как было уже сказано, мы займемся изучением перекрестных запросов. Для чего же нужны эти перекрестные запросы? Чтобы помочь Вам ответить на этот вопрос, я приведу пример, чтобы Вы могли наглядно увидеть применение этих перекрестных запросов.

Допустим, у Вас есть таблица следующего вида (колонки называются god, chislo, tovar, сама таблица называется tabl).

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

Пример перекрестного запроса с использованием CASE WHEN

Существует несколько способов реализовать перекрестный запрос. Например, следующий, он подойдет практически для любой версии СУБД. Для нашей с Вами тестовой задачи запрос будет выглядеть так:

  
  SELECT tovar,
           SUM (CASE god WHEN '2009' THEN chislo ELSE 0 END) AS "2009",
           SUM (CASE god WHEN '2010' THEN chislo ELSE 0 END) AS "2010",
           SUM (CASE god WHEN '2011' THEN chislo ELSE 0 END) AS "2011"
  FROM tabl 
  GROUP BY tovar

В результате вы получите следующий вывод данных:

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

Примечание! Сразу скажу, что все перечисленные в данной статье запросы вы сможете применить, только если Вы заранее знаете, сколько у Вас будет выводиться столбцов (у нас это «года» и их всего 3, если было 4, то нам пришлось бы добавлять в запрос еще одну строку и так далее), т.е. только фиксированное количество столбцов, которое Вам заранее известно. А если Вы не знаете, сколько необходимо выводить лет или их количество будет постоянно меняться, Вам уже нужно будет писать динамически расширяемый перекрестный запрос с помощью специальных процедур. В данном уроке такой способ мы рассматривать не будем.

Пример перекрестного запроса с использованием оператора PIVOT

Переходим к следующему способу, который появился с выходом Microsoft SQL Server 2005. В более ранних версиях этот способ применить нельзя, так как там отсутствовал оператор PIVOT. С использованием этого способа запрос будет выглядеть следующим образом, и результат будет тот же самый:

  
  SELECT tovar, [2009], [2010], [2011]
  FROM tabl
  PIVOT(SUM (chislo) FOR god IN ([2009], [2010], [2011])) AS mytabl

Как видите этот запрос уже немного покороче, но у него своеобразный синтаксис (если конкретней, то у оператора PIVOT).

Если Вы работаете в Access, то там вообще все просто, это можно реализовать стандартными средствами Access, даже если Вы не знаете SQL. Просто нажмите «Создать новый запрос» и выберете «Перекрестный запрос». После создания запроса можете открыть запрос в режиме конструктора и перейти в режим SQL, где Вы сможете увидеть сам запрос на SQL.

На сегодня о перекрестных запросах я думаю достаточно. Продолжим изучение SQL в следующих материалах.