Перекрестные запросы в 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 2. @ table = 'Sales' , 3. @onrows = 'MONTH(Date)' , 4. @onrowsalias = 'SaleMonth' , 5. @oncols = 'YEAR(Date)' , 6. @sumcol = 'Amount' 7. GO |