Необходимо из исходной
таблицы получить сводную таблицу (см. рис.). В качестве столбцов в новой таблице будут годы, в строках названия клиентов, а в
ячейках – суммы по полю [Count],
соответствующие данному клиенту и году. Сложность заключается в том, что
неизвестно количество лет, следовательно, количество столбцов в новой таблице должно
вычисляться динамически.
После некоторых размышлений и исследования возможностей Microsoft SQL Server 2008 R2 был разработан следующий код:
--Создаем тестовую таблицу CREATE TABLE Table1 ( [Customer] VARCHAR(10), [MONTH] INT, [YEAR] INT, [COUNT] INT ) GO --вставляем данные в таблицу INSERT INTO Table1([Customer],[MONTH],[YEAR],[COUNT]) VALUES ('A',9,2011,1), ('A',9,2011,8), ('A',9,2012,1), ('B',9,2011,3), ('B',10,2012,2), ('B',10,2012,2) GO --создаем переменную для хранения строки с заголовками столбцов DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns + ',[' + CAST([YEAR] AS VARCHAR) + ']', '[' + CAST([YEAR] AS VARCHAR)+ ']') FROM Table1 GROUP BY [YEAR] DECLARE @query NVARCHAR(4000) --динамически конструируем текст запроса SET @query = 'select * from (select [customer],[Year],[Count] from Table1 )AS SourceTable Pivot(sum([count]) for [Year] IN (' + @columns + ')) AS PVT;' --выполнение запроса с помощью хранимой процедуры EXECUTE SP_EXECUTESQL @query
Данный код создает таблицу Table1, соответствующую исходной таблице на рисунке. Затем
переменной @COLUMNS присваивается строка с годами через запятую, которые считываются запросом, с применением функции COALESCE. После этого можно сконструировать запрос, создающий сводную таблицу. Данный запрос в виде строки сохраняется в переменной @query и выполняется посредством хранимой процедуры sp_executesql. Результат выполнения запроса:
2 комментария :
Спасибо! Помогло решить задачу для построения отчета.
спасибо
Отправить комментарий