пятница, 5 октября 2012 г.

Динамический PIVOT в Microsoft SQL Server


Необходимо из исходной таблицы получить сводную таблицу (см. рис.).  В качестве столбцов в новой таблице  будут годы, в строках названия клиентов, а в ячейках – суммы по полю [Count], соответствующие данному клиенту и году. Сложность заключается в том, что неизвестно количество лет, следовательно,  количество столбцов в новой таблице должно вычисляться динамически.


После некоторых размышлений и исследования возможностей Microsoft SQL Server  2008 R2 был разработан следующий код:

  1. --Создаем тестовую таблицу
  2. CREATE TABLE Table1
  3. (
  4. [Customer] VARCHAR(10),
  5. [MONTH] INT,
  6. [YEAR] INT,
  7. [COUNT] INT
  8. )
  9. GO
  10. --вставляем данные в таблицу
  11. INSERT INTO Table1([Customer],[MONTH],[YEAR],[COUNT])
  12. VALUES ('A',9,2011,1),
  13. ('A',9,2011,8),
  14. ('A',9,2012,1),
  15. ('B',9,2011,3),
  16. ('B',10,2012,2),
  17. ('B',10,2012,2)
  18. GO
  19. --создаем переменную для хранения строки с заголовками столбцов
  20. DECLARE @columns VARCHAR(8000)
  21. SELECT @columns = COALESCE(@columns + ',[' + CAST([YEAR] AS VARCHAR) + ']', '[' + CAST([YEAR] AS VARCHAR)+ ']')
  22. FROM Table1
  23. GROUP BY [YEAR]
  24. DECLARE @query NVARCHAR(4000)
  25. --динамически конструируем текст запроса
  26. SET @query = 'select * from (select
  27. [customer],[Year],[Count] from Table1
  28. )AS SourceTable
  29. Pivot(sum([count]) for [Year] IN (' + @columns + ')) AS PVT;'
  30. --выполнение запроса с помощью хранимой процедуры
  31. EXECUTE SP_EXECUTESQL @query




Данный код создает таблицу Table1, соответствующую исходной таблице на рисунке. Затем 
переменной @COLUMNS присваивается строка с  годами через запятую, которые считываются запросом, с применением функции COALESCE. После этого можно сконструировать запрос, создающий сводную таблицу. Данный запрос в виде строки сохраняется в переменной @query и выполняется посредством хранимой процедуры sp_executesql. Результат выполнения запроса:


2 комментария :

Unknown комментирует...

Спасибо! Помогло решить задачу для построения отчета.

Анонимный комментирует...

спасибо

Каталог блогов Blogolist