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

Источник: t-sql

Перекрестные запросы ( Crosstab Query ) являются еще одной специфической разновидностью запросов на выборку. Предназначены они для более глубокого анализа информации, хранящейся в таблицах. Ключевым словом SQL-оператора перекрестного запроса, задающим его тип, является слово TRANSFORM (преобразовать). Это подразумевает, что значения одного из столбцов (полей) выборки, будут преобразованы в названия столбцов итоговой выборки. Результаты перекрестного запроса группируются по двум наборам данных, один из которых расположен в левом столбце (столбцах) таблицы, а второй - в верхней строке. В остальном пространстве таблицы отображаются результаты статистических расчетов (Sum, Count и т.д.), выполненных над данными трансформированного поля.

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

01.CREATE TABLE Sales

02.(

03.SaleID int IDENTITY PRIMARY KEY CLUSTERED,

04.ClientID int,

05.Date datetime,

06.Amount money

07.)

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

09.insert Sales values(1,'20020302', 134.01)

10.insert Sales values(1,'20031003', 2346.03)

11.insert Sales values(2,'20030203', 754.88)

12.insert Sales values(3,'20010301', 73.07)

13.insert Sales values(3,'20030402', 734.46)

14.insert Sales values(4,'20010301', 1567.10)

15.insert Sales values(4,'20020404', 6575.70)

16.insert Sales values(4,'20030307', 6575.77)

17.insert Sales values(4,'20030309', 6575.37)

18.insert Sales values(5,'20011201', 1975.73)

19.insert Sales values(5,'20030306', 178965.63)

20.insert Sales values(6,'20020103', 16785.34)

21.insert Sales values(6,'20030304', 1705.44)

22.GO

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

01.SELECT

02. MONTH(Date) AS SaleMonth,

03. SUM(CASE YEAR(Date)

04. WHEN 2001 THEN Amount

05. ELSE 0

06. END) AS [2001],

07. SUM(CASE YEAR(Date)

08. WHEN 2002 THEN Amount

09. ELSE 0

10. END) AS [2002],

11. SUM(CASE YEAR(Date)

12. WHEN 2003 THEN Amount

13. ELSE 0

14. END) AS [2003]

15.FROM Sales

16.GROUP BY MONTH(Date)

17.ORDER BY MONTH(Date)

18.GO

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

01.SaleMonth 2001 2002 2003

02.----------- --------------------- --------------------- ---------------------

03.1 .0000 16785.3400 .0000

04.2 .0000 .0000 754.8800

05.3 1640.1700 134.0100 193822.2100

06.4 15.4800 6575.7000 734.4600

07.10 .0000 .0000 2346.0300

08.12 1975.7300 .0000 .0000

09.

10.(6 row(s) affected)

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

1.SELECT * FROM

2.(SELECT YEAR(Date) y, MONTH(Date) SaleMonth, Amount FROM Sales) as s

3.PIVOT

4.(SUM(Amount) for y in ([2001], [2002], [2003])) pv

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

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

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

01.CREATE PROCEDURE sp_CrossTab

02. @table AS sysname,-- Таблица для построения crosstab отчета

03. @onrows AS nvarchar(128),-- Значение для группировки по строкам

04. @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки

05. @oncols AS nvarchar(128),-- Значение для группировки по колонкам

06. @sumcol AS sysname = NULL -- Значение для суммирования

07.AS

08.SET NOCOUNT ON

09.DECLARE

10. @sql AS varchar(8000),

11. @NEWLINE AS char(1)

12.

13.SET @NEWLINE = CHAR(10)

14.

15.-- Шаг 1: начало строки SQL.

16.SET @sql =

17. 'SELECT' + @NEWLINE +

18. ' ' + @onrows +

19. CASE

20. WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias

21. ELSE ''

22. END

23.

24.-- Шаг 2: Хранение ключей во временной таблице.

25.CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

26.

27.DECLARE @keyssql AS varchar(1000)

28.SET @keyssql =

29. 'INSERT INTO #keys ' +

30. 'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +

31. 'FROM ' + @table

32.

33.EXEC (@keyssql)

34.

35.-- Шаг 3: Средняя часть строки SQL.

36.DECLARE @key AS nvarchar(100)

37.SELECT @key = MIN(keyvalue) FROM #keys

38.

39.WHILE @key IS NOT NULL

40.BEGIN

41. SET @sql = @sql + ',' + @NEWLINE +

42. ' SUM(CASE CAST(' + @oncols +

43. ' AS nvarchar(100))' + @NEWLINE +

44. ' WHEN N''' + @key +

45. ''' THEN ' + CASE

46. WHEN @sumcol IS NULL THEN '1'

47. ELSE @sumcol

48. END + @NEWLINE +

49. ' ELSE 0' + @NEWLINE +

50. ' END) AS [' + @key+']'

51.

52. SELECT @key = MIN(keyvalue) FROM #keys

53. WHERE keyvalue > @key

54.END

55.

56.-- Шаг 4: Конец строки SQL.

57.SET @sql = @sql + @NEWLINE +

58. 'FROM ' + @table + @NEWLINE +

59. 'GROUP BY ' + @onrows + @NEWLINE +

60. 'ORDER BY ' + @onrows

61.

62.SET NOCOUNT OFF

63.PRINT @sql + @NEWLINE-- для отладки

64.EXEC (@sql)

65.GO

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

1.EXEC sp_CrossTab

2. @table = 'Sales',

3. @onrows = 'MONTH(Date)',

4. @onrowsalias = 'SaleMonth',

5. @oncols = 'YEAR(Date)',

6. @sumcol = 'Amount'

7.GO

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

01.ALTER PROCEDURE sp_CrossTab_PIVOT

02. @table AS sysname,-- Таблица для построения crosstab отчета

03. @onrows AS nvarchar(128),-- Значение для группировки по строкам

04. @onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки

05. @oncols AS nvarchar(128),-- Значение для группировки по колонкам

06. @sumcol AS sysname = NULL -- Значение для суммирования

07.AS

08.SET NOCOUNT ON

09.DECLARE

10. @sql AS nvarchar (max),

11. @case AS varchar(1000)

12.SET @case=''

13.SELECT @sql='

14.SELECT @case=@case+''[''+CONVERT(VARCHAR, '+@oncols+')+''], '''+

15.' FROM '+@table+' GROUP BY '+@oncols+' ORDER BY '+@oncols

16.

17.EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out

18.SET @case=LEFT(@case, LEN(@case)-1)

19.

20.SELECT @sql='SELECT * FROM (

21.SELECT '+@oncols+' y, '+@onrows+' '+@onrowsalias+', '+@sumcol+' FROM '+@table+

22.') as s

23.PIVOT

24.(SUM ('+@sumcol+') for y in ('+@case+')) as pv'

25.PRINT @sql -- для отладки

26.EXECUTE (@sql)

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

1.EXEC sp_CrossTab_PIVOT