Перекрестные запросы в Т-SQL

by Alexey Knyazev 20. октября 2009 22:15

Перекрестные запросы (Crosstab Query) являются еще одной специфической разновидностью запросов на выборку. Предназначены они для более глубокого анализа информации, хранящейся в таблицах.

Ключевым словом SQL-оператора перекрестного запроса, задающим его тип, является слово TRANSFORM (преобразовать). Это подразумевает, что значения одного из столбцов (полей) выборки, будут преобразованы в названия столбцов итоговой выборки.

Результаты перекрестного запроса группируются по двум наборам данных, один из которых расположен в левом столбце (столбцах) таблицы, а второй — в верхней строке. В остальном пространстве таблицы отображаются результаты статистических расчетов (Sum, Count и т.д.), выполненных над данными трансформированного поля.

Для демонстрации создадим тестовую таблицу:

CREATE TABLE Sales
(
SaleID int IDENTITY PRIMARY KEY CLUSTERED,
ClientID int,
Date datetime,
Amount money
)
insert Sales values(1,'20010401', 15.48)
insert Sales values(1,'20020302', 134.01)
insert Sales values(1,'20031003', 2346.03)
insert Sales values(2,'20030203', 754.88)
insert Sales values(3,'20010301', 73.07)
insert Sales values(3,'20030402', 734.46)
insert Sales values(4,'20010301', 1567.10)
insert Sales values(4,'20020404', 6575.70)
insert Sales values(4,'20030307', 6575.77)
insert Sales values(4,'20030309', 6575.37)
insert Sales values(5,'20011201', 1975.73)
insert Sales values(5,'20030306', 178965.63)
insert Sales values(6,'20020103', 16785.34)
insert Sales values(6,'20030304', 1705.44)
GO

До версии SQL Server 2005, в котором появился оператор PIVOT, перекрестные запросы выполнялись через оператор CASE (BOL->Cross-Tab Reports):

SELECT
  MONTH(Date) AS SaleMonth,
  SUM(CASE YEAR(Date)
        WHEN 2001 THEN Amount
        ELSE 0
      END) AS [2001],
  SUM(CASE YEAR(Date)
        WHEN 2002 THEN Amount
        ELSE 0
      END) AS [2002],
  SUM(CASE YEAR(Date)
        WHEN 2003 THEN Amount
        ELSE 0
      END) AS [2003]
FROM Sales
GROUP BY MONTH(Date)
ORDER BY MONTH(Date)
GO

Результат выполнения запросa:


SaleMonth   2001                  2002                  2003                 
----------- --------------------- --------------------- ---------------------
1           .0000                 16785.3400            .0000
2           .0000                 .0000                 754.8800
3           1640.1700             134.0100              193822.2100
4           15.4800               6575.7000             734.4600
10          .0000                 .0000                 2346.0300
12          1975.7300             .0000                 .0000

(6 row(s) affected)

Теперь тот же запрос, но уже с использованием оператора PIVOT (версия SQL Server 2005 и выше):

SELECT * FROM
(SELECT YEAR(Date) y, MONTH(Date) SaleMonth, Amount FROM Sales) as s
PIVOT
(SUM(Amount) for y in ([2001], [2002], [2003])) pv

Всё бы хорошо, когда мы за ранее знаем кол-во лет, но что делать, если мы не имеем такой информации на входе, либо нужно, чтобы код был более универсальным и при добавлении записей с более старшей датой код был "рабочим". Выход-динамический перекрестный запрос (Dynamic Crosstab Queries). Усложним наши тестовые данные добавив хотя бы одну запись с другим годом:

insert Sales values(1,'20010401', 15.48)

Очень интересное решение этой задачи предложил Ицик Бен-Ган (http://am.rusimport.ru/MsAccess/topic.aspx?id=285):

CREATE PROCEDURE sp_CrossTab
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS varchar(8000),
  @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- Шаг 1: начало строки SQL.
SET @sql =
  'SELECT' + @NEWLINE +
  '  ' + @onrows +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END

-- Шаг 2: Хранение ключей во временной таблице.
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

EXEC (@keyssql)

-- Шаг 3: Средняя часть строки SQL.
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ','                   + @NEWLINE +
    '  SUM(CASE CAST(' + @oncols +
                     ' AS nvarchar(100))' + @NEWLINE +
    '        WHEN N''' + @key +
           ''' THEN ' + CASE
                          WHEN @sumcol IS NULL THEN '1'
                          ELSE @sumcol
                        END + @NEWLINE +
    '        ELSE 0'                      + @NEWLINE +
    '      END) AS [' + @key+']'

  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

-- Шаг 4: Конец строки SQL.
SET @sql = @sql         + @NEWLINE +
  'FROM ' + @table      + @NEWLINE +
  'GROUP BY ' + @onrows + @NEWLINE +
  'ORDER BY ' + @onrows

SET NOCOUNT OFF
PRINT @sql  + @NEWLINE-- для отладки
EXEC (@sql)
GO

Вызов этой процедуры:

EXEC sp_CrossTab
  @table       = 'Sales',
  @onrows      = 'MONTH(Date)',
  @onrowsalias = 'SaleMonth',
  @oncols      = 'YEAR(Date)',
  @sumcol      = 'Amount'
GO

 Но хотелось бы немного упростить эту процедуру с помощью оператора PIVOT и вот, что получилось:

CREATE PROCEDURE sp_CrossTab_PIVOT
  @table       AS sysname,-- Таблица для построения crosstab отчета
  @onrows      AS nvarchar(128),-- Значение для группировки по строкам
  @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS nvarchar(128),-- Значение для группировки по колонкам
  @sumcol      AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS nvarchar (max),
  @case AS varchar(1000)
SET @case=''
SELECT @sql='
SELECT @case=@case+''[''+CONVERT(VARCHAR, '+@oncols+')+''], '''+
' FROM '+@table+' GROUP BY '+@oncols+' ORDER BY '+@oncols
 
EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out
SET @case=LEFT(@case, LEN(@case)-1)

SELECT @sql='SELECT * FROM (
SELECT '+@oncols+' y, '+@onrows+' '+@onrowsalias+', '+@sumcol+' FROM '+@table+
') as s
PIVOT
(SUM ('+@sumcol+') for y in ('+@case+')) as pv'
PRINT @sql -- для отладки 
EXECUTE (@sql)

Вызов моей процедуры идентичен вызову предыдущей процедуры:

EXEC sp_CrossTab_PIVOT
  @table       = 'Sales',
  @onrows      = 'MONTH(Date)',
  @onrowsalias = 'SaleMonth',
  @oncols      = 'YEAR(Date)',
  @sumcol      = 'Amount'
GO

Ещё одно решение этой задачи можно найти в FAQ на сайте SQL.RU (http://www.sql.ru/faq/faq_topic.aspx?fid=358)

Tags: ,

SQL Server

Комментарии (2) -

Admin
Admin Russia
08.04.2010 19:53:49 #

Динамический UNPIVOT




--Для демонстрации в контексте БД tempdb
USE tempdb
GO
--Создаём тестовую таблицу
create table t(idno int, a int, b int, c int)
insert into t
select 1, 1, 2, 3
union all
select 2, 4, 5, 6
union all
select 3, 7, 8, 9
go

--Создаём процедуру
CREATE PROCEDURE sp_CrossTab_UNPIVOT
  @table     AS sysname, --Имя таблицы
  @col1      AS nvarchar(128), --Имя Колонки на выходе
  @col2      AS nvarchar(128), --Колонка исключение
  @val       AS nvarchar(128) --Имя колонки для значений

AS
SET NOCOUNT ON
DECLARE
  @sql AS nvarchar (max),
  @case AS varchar(1000)
SET @case=''
SELECT @sql='SELECT @case=@case+QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='''+@table+''''+' AND COLUMN_NAME!='''+@col2+''''
EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out
SELECT @case=REPLACE(@case,'][','],[')
SELECT @sql='select * from '+@table+' unpivot ('+@val+' for '+@col1+' in ('+@case+') ) pv'
EXEC (@sql)
GO

--Запускаем процедуру
sp_CrossTab_UNPIVOT @table='t', @col1='col_name', @col2='idno', @val='value'
GO

--Удаляем процедуру
DROP PROCEDURE sp_CrossTab_UNPIVOT
GO
--Удаляем тестовую таблицу
DROP TABLE t
GO

Reply

Яковлев Дмитрий
Яковлев Дмитрий Ukraine
21.07.2014 19:15:13 #

Знаю что почти 5 лет прошло от данного поста Smile, только недавно на данный сайт наткнулся.

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

/* Создание  таблицы для примера и наполнение её */
if object_id('tempdb..#CTM') is not null drop table #CTM
create table #CTM
  (
  FilID    Int,
  LagerID    Int,
  Price    float
  )


insert into #CTM
values  (666,5,47),
    (6,4,3),
    (8,97,3.5)

/* Создаём параметр, где мы описываем поля, которые должны быть столбцами и которые внесём в курсор */
DECLARE @R VARCHAR(5000), @ST VARCHAR(5000)
SELECT @R = ISNULL(@R,'')+ CASE WHEN @R IS NULL THEN '' ELSE ', ' END + '['+cast (filid AS varchar(100))+']'
FROM (
    select distinct
      filid
    from #CTM
   ) a

/* Собственно сам курсор */
SET @ST = '
select
  ppp.*
from(
    select
      lagerid, filid, Price
    from #CTM f  ) a
pivot(
  avg(price)
  for filid in ('+ @R +')
  ) as ppp  '
EXEC (@ST)

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading