Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разворот таблицы своими руками  [new]
Poroh
Member

Откуда:
Сообщений: 6
В начале моей работы программистом на Sql Server 2000, меня расстраивало отсутствие штатных функций создания сводной таблицы, которая может быть крайне полезна для построения настраиваемых пользователем отчетов.
Тогда я разработал собственную процедуру usp_pivot, при помощи которой я быстро смог решать все задачи подобного рода на MSSQL2000, а теперь решаю на MSSQL2005.

Предлагаемое решение не претендует на идеальность и тем более не является стандартным, но, тем не менее, в ряде случаев позволяет программисту сэкономить много сил и времени.

Возможно существуют и стандартные функции, позволяющие сделать то же самое - честно говорю, я их не искал - мне с головой хватало собственного решения.

Если процедура кого-либо заинтересует, могу дать по её использованию больше данных - пишите.

С уважением, Прохор

Сама процедура:
create procedure [dbo].[dt] (@tab varchar(255)) as
exec( 'if object_id(''tempDB..'+@tab+''') is not null drop table '+@tab)
go
create procedure [dbo].[usp_pivot] 
   @sql_init           varchar(max), -- Запрос, на основе которого строится результат
   @pivot_name_fields  varchar(max), -- "Разворачиваемые" поля
   @pivot_value_fields varchar(max), -- Поля значения которых будут распихиваться по развернутым полям
   @base_fields        varchar(max), -- Неразворачиваемые поля
   @pivot_value_agg    varchar(max) = null, -- aggregations для @pivot_value_fields (по умолчанию max)
   @output_total_name  varchar(max)  = null, -- Имя для обобщенного столбца, если null то обобщенные столбцы не выводить
   @into               varchar(max) = null  -- Вывести в таблицу. Для временных таблиц вида - tempdb..#mat
-- Автор Лейкин П.Л. формирует сводную таблицу...
-- Может разворачивать только по одному полю.
-- В качестве агрегационной функции используется max (не sum...) 
-- Для использование sum в изначальном наборе данных должно быть количество записей обеспечивающее однозначное определение ячейки
as
begin
   exec dt #pfnv 
   exec dt #pvfn
   exec dt #pvf
   exec dt #pfspt
   exec dt #sqlpt
   
   
   declare @pf varchar(max), @of varchar(max)
   declare @pi int,          @oi int,         @is varchar(max)
   
   create table #pfnv (i int identity, pfi int, value varchar(max), name varchar(max), vname varchar(max) null, result int) /* pivot field names (by values)*/
   set @pi = 0
   set @pf = ltrim(rtrim(nullif(dbo.udf_substring_delimited(@pivot_name_fields, ',', @pi), '')))
   set @is = cast(@pi as varchar(max))
   while @pf is not null 
   begin

      exec('insert into #pfnv (pfi, value, name, result) ' + 
           'select i, cast(' + @pf + ' as varchar(max)), ''' + @pf + ''', 0 ' + 
           'from ' + 
              '(select distinct i = ' + @is + ', ' + @pf + ' from ( ' + @sql_init + ') a) a ' + 
           'order by ' + @pf)
      if isnull(@output_total_name, '') <> '' 
         insert into #pfnv (pfi, value, name, result)  
         select @is, @output_total_name, @output_total_name, 1 

      set @pi = @pi + 1
      set @is = cast(@pi as varchar(max))
      set @pf = ltrim(rtrim(nullif(dbo.udf_substring_delimited(@pivot_name_fields, ',', @pi), '')))
   end

   update #pfnv set vname = case when @pi = 1 then value else name + '_' + value end
   
   --<> Собираем имена полей данных сводной таблицы
   --<<> формируем имена полей сводной таблицы
   create table #pvfn (fi int, name varchar(max), agg varchar(max), vname varchar(max)) /*pivot value field names*/
   set @pi = 0
   set @pf = ltrim(rtrim(nullif(dbo.udf_substring_delimited(@pivot_value_fields, ',', @pi), '')))
   while @pf is not null 
   begin
      declare @pfa varchar(max)
      set @pf  = ltrim(rtrim(nullif(dbo.udf_substring_delimited(@pivot_value_fields, ',', @pi), '')))
      set @pfa = ltrim(rtrim(nullif(dbo.udf_substring_delimited(@pivot_value_agg,    ',', @pi), '')))      
      set @pfa = isnull(nullif(@pfa, ''), 'max')

      insert into #pvfn select cast(@pi as varchar(max)), @pf, @pfa, '_' + @pf

      set @pi = @pi + 1
      set @pf = ltrim(rtrim(nullif(dbo.udf_substring_delimited(@pivot_value_fields, ',', @pi), '')))
   end
   if @pi = 1
      update #pvfn set vname = ''

   create table #pfspt (i int identity, fn varchar(max)/*field name*/, agg varchar(max), script varchar(max)) /*pivot field setup*/
   insert into #pfspt (fn, agg, script)
   select 
      fn = '[' + n.vname + v.vname + ']', 
      v.agg, 
      script = case when n.result = 0
                    then n.name + ' = ''' + n.value + ''' then ' + v.name  
                    else '1 = 1 then ' + v.name end
   from 
      #pfnv n, #pvfn v


   order by 
      n.i, v.fi
   
   create table #sqlpt (i int identity, s varchar(max))

   if isnull(@into, '') <> '' and object_id(@into) is not null
      insert #sqlpt (s)
      select ' insert into ' + @into + ' ' 
   
   insert #sqlpt (s) 
   select 'select ' + @base_fields 
   insert #sqlpt (s)
   select ', ' + fn + ' = ' + agg + '(case when ' + script + ' else null end)' from #pfspt order by i
   
   if isnull(@into, '') <> '' and object_id(@into) is null
      insert #sqlpt (s)
      select ' into ' + @into + ' ' 

   insert #sqlpt (s)
   select ' from (' + @sql_init + ') a where ' + replace(@base_fields, ',', ' is not null or ') + ' is not null group by '+  @base_fields

   insert #sqlpt (s)
   select ' order by ' + @base_fields 
   
   declare @res_sql	varchar(max)
   select @res_sql = isnull(@res_sql, '') + s from #sqlpt
   exec(@res_sql)

   exec dt #sqlpt
   exec dt #pfnv 
   exec dt #pvfn
   exec dt #pvf 
   exec dt #pfspt
end


Пример использования:
exec dt #ttt
go
select week_num = 10, supplier = 'Вася', amount = 500, dept = 100
into #ttt
union select 10, 'Петя', 400, 400
union select 10, 'Коля', 333, 333
union select 10, 'Олег', 22, null
union select 10, 'Олег', 2258, null
union select 11, 'Костя', 650, 250
union select 11, 'Петя', 4000, 4000
union select 11, 'Олег', 280, 280
union select 12, 'Костя', 34, 34
union select 12, 'Петя', 25, 25
union select 13, 'Олег', null, 1 
go
exec usp_pivot
   @sql_init           = 'select * from #ttt', 
   @pivot_name_fields  = 'week_num', 
   @pivot_value_fields = 'amount, dept', 
   @base_fields        = 'Supplier', 
   @pivot_value_agg    = 'sum' 


Результат:
Supplier 10_amount 10_dept 11_amount 11_dept 12_amount 12_dept 13_amount 13_dept
Вася 500 100 NULL NULL NULL NULL NULL NULL
Коля 333 333 NULL NULL NULL NULL NULL NULL
Костя NULL NULL 650 250 34 34 NULL NULL
Олег 2280 NULL 280 280 NULL NULL NULL 1
Петя 400 400 4000 4000 25 25 NULL NULL
4 июн 12, 16:01    [12662615]     Ответить | Цитировать Сообщить модератору
 Re: Разворот таблицы своими руками  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
дык боянище:
Ицик Бен-Ган в 2001ом решение предлагал и динамически
http://am.rusimport.ru/MsAccess/topic.aspx?id=285

а с 2005ого есть стандартный pivot

и тут есть фак на эту тему: https://www.sql.ru/faq/faq_topic.aspx?fid=571
4 июн 12, 16:13    [12662740]     Ответить | Цитировать Сообщить модератору
 Re: Разворот таблицы своими руками  [new]
Poroh
Member

Откуда:
Сообщений: 6
Алексей, спасибо большое за ссылки.

Мне тут пришло время ещё раз разворачивать табличку. вида похожего на тот, что приведен в примере выше, только намного бОльшую.

Хотел попробовать стандартным pivot, но не увидел в мануале как им развернуть таблицу в произвольное число столбцов.
Буду благодарен, если кинете ссылку с тем, как это можно сделать с помощью этого стандартного pivot.


Потом посмотрел вторую ссылку - там @sql ограничен 4000 тыс. символами, а мне этого мало.

Моя же процедура развернула мой набор данных в таблицу примерно в 1000 столбцов и 100000 строк и не подавилась.
На практике такие конечно не нужны, но тем не менее.
4 июл 12, 17:29    [12817696]     Ответить | Цитировать Сообщить модератору
 Re: Разворот таблицы своими руками  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
http://www.t-sql.ru/post/Crosstab.aspx
4 июл 12, 21:34    [12818931]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить