Microsoft SQL Server
Скрипты
T-SQL

Перекрестные запросы

Опубликовано: 11 ноя 05
Рейтинг:

Автор: Talen
Прислал:

Хочу привести еще один пример реализации перекрестных запросов средствами T-SQL.

Приведенная ниже процедура реализует функцию TRANSFORM, почти как в Microsoft Access , а именно:

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

    Недостатки:
  • Необходимость создания временной таблицы;
  • Невозможно использовать временную (которая #) таблицу в качестве входных данных;
  • Используется sp_executesql, а значит строка результирующего запроса не может превышать 4000 символов в кодировке UNICODE.
    _____________
    Параметры вызова:
    exec sp_transform
        @Table sysname,                   --<имя таблицы с исходными данными>
        @Rows varchar(1024),              --<столбцы результирующего набора,разделенные запятой>
        @Cols sysname,                    --<трансформируемый столбец>
        @RowValue sysname,                --<столбец значений>
        @Function sysname,                --<агрегатная функция (sum,count,avg,...)> 
        @DestTable sysname = null,        --[имя таблицы для записи результата]
        @UsedRows varchar(1024) = null    --[Набор трансформируемых столбцов, разделенных запятой]
    
    _____________
    Пример:
    if object_id('tempdb..##test_transform') is not null
    	drop table ##test_transform
    
    create table ##test_transform (Department varchar(10), Articul varchar(10), Title varchar(20), Weight float, Qty int)
    
    insert into ##test_transform (Department, Articul, Title, Weight, Qty)
    	values ('Цех 1', 'ART1', 'Булочки с маком', 50, 1000)
    insert into ##test_transform (Department, Articul, Title, Weight, Qty)
    	values ('Цех 2', 'ART1', 'Булочки с маком', 50, 2000)
    insert into ##test_transform (Department, Articul, Title, Weight, Qty)
    	values ('Цех 2', 'ART2', 'Булочки с изюмом', 60, 700)
    insert into ##test_transform (Department, Articul, Title, Weight, Qty)
    	values ('Цех 3', 'ART2', 'Булочки с изюмом', 50, 750)
    insert into ##test_transform (Department, Articul, Title, Weight, Qty)
    	values ('Цех 4', 'ART3', 'Торт с кремом', 700, 200)
    insert into ##test_transform (Department, Articul, Title, Weight, Qty)
    	values ('Цех 4', 'ART3', 'Торт с кремом', 700, 50)
    
    --см. Таблица №1
    select * from ##test_transform
    
    --см. Таблица №2
    exec sp_transform '##test_transform', 'Articul,Title,Weight', 'Department', 'Qty', 'sum'
    
    --Если необходимо сразу указать трансформируемые столбцы
    exec sp_transform '##test_transform', 'Articul,Title,Weight', 'Department', 'Qty', 'sum', '##transform_result', 'Цех 1,Цех 2,Цех 3,Цех 4,Цех 5'
    
    --см. Таблица №3
    select Articul,Title,Weight,[Цех 1],[Цех 2],[Цех 3],[Цех 4],[Цех 5]  from ##transform_result
    
    drop table ##test_transform
    drop table ##transform_result
    
    _____________
    Результат:

    Таблица №1
    DepartmentArticulTitleWeightQty
    Цех 1ART1Булочки с маком50.01000
    Цех 2ART1Булочки с маком50.02000
    Цех 2ART2Булочки с изюмом60.0700
    Цех 3ART2Булочки с изюмом50.0750
    Цех 4ART3Торт с кремом700.0200
    Цех 4ART3Торт с кремом700.050


    Таблица №2
    ArticulTitleWeightЦех 1Цех 2Цех 3Цех 4
    ART1Булочки с маком50.010002000
    ART2Булочки с изюмом50.0750
    ART2Булочки с изюмом60.0700
    ART3Торт с кремом700.0250


    Таблица №3
    ArticulTitleWeightЦех 1Цех 2Цех 3Цех 4Цех 5
    ART1Булочки с маком50.010002000
    ART2Булочки с изюмом50.0750
    ART2Булочки с изюмом60.0700
    ART3Торт с кремом700.0250

    _____________
    Функция довольно проста и создана была для личного применения, но она вполне работоспособна. Если кому-то пригодится, то буду очень рад.
    _____________
    Собственно, процедура:
    create procedure dbo.sp_transform 
    	@Table sysname,
    	@Rows varchar(1024),
    	@Cols sysname,
    	@RowValue sysname,
    	@Function sysname,
    	@DestTable sysname = null,
    	@UsedRows varchar(1024) = null
    as
    begin
    	set nocount on
    
    	declare @sql nvarchar(4000)
    	declare @Key varchar(100)
    	declare @newline char(1)
    
    	set @newline = char(10)
    
    	if @UsedRows is null
    	begin
    		set @sql = N'select distinct ' + @Cols + N' as keys into ##transform_keys from ' + @Table + ' order by ' + @Cols
    		exec sp_executesql @sql
    	end
    	else
    	begin
    		declare @i int
    		declare @row sysname
    		set @i = charindex(',', @UsedRows)
    		create table ##transform_keys (keys sysname primary key)
    		while @i <> 0
    		begin
    			set @row = ltrim(rtrim(substring(@UsedRows, 1, @i-1)))
    			set @UsedRows = ltrim(rtrim(substring(@UsedRows, @i+1, len(@UsedRows))))
    
    			if (@row is not null) and (@row <> '')
    				insert into ##transform_keys values (@row)
    
    			set @i = charindex(',', @UsedRows)
    		end
    		if (@UsedRows is not null) and (@UsedRows <> '')
    			insert into ##transform_keys values (@UsedRows)
    	end
    
    	set @sql = 'select ' + @Rows + @newline
    
    	set @Key = null
    	select top 1 @Key = keys from ##transform_keys
    
    	while @Key is not null
    	begin
      		set @sql = @sql + ','+@Function+'(case when convert(varchar,' + @Cols + ')=convert(varchar,''' + @Key + ''') then ' + @RowValue + ' else Null end) [' + @Key + ']' + @newline
    		--print @sql
    
    		delete from ##transform_keys where keys = @Key
    		set @Key = null
    		select top 1 @Key = keys from ##transform_keys
    	end
    
    	if @DestTable is not null
    		set @sql = @sql + 'into ' + @DestTable + @newline
    	set @sql = @sql + 'from ' + @Table + @newline + 'group by ' + @Rows + @newline + 'order by ' + @Rows
    	
    	print @sql
    	exec  sp_executesql @sql
    
    	drop table ##transform_keys  
    end
    
    _____________
    PS: Хочу оговориться, что при размещении данной статьи я не преследовал цели попасть в историю или самоутвердиться.
    Просто предложил простое и быстрое решение наболевшего для некоторых вопроса.

  • Комментарии


    • Невозможно использовать временную (которая #) таблицу в качестве входных данных;
      А почему нельзя? У меня всё работает:
      exec transp '#tempParams', 'id_object', 'name', 'value','Max'

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

      Правда тогда скрипт получится разношерстный.... Хотя, если подумать, то курсор то можно создать и на основе временной таблицы. И при создании конечного запроса будет использоваться курсор в обоих случаях.

    • Используется sp_executesql, а значит строка результирующего запроса не может превышать 4000 символов в кодировке UNICODE.

      а если использовать EXEC (@sql)?

      P.S. прошу сильно не ругать, если я сморозил глупость.

    • set @UsedRows = ltrim(rtrim(substring(@UsedRows, @i+1, len(@UsedRows))))

      мне кажется будет более разумно добавить -1 к размеру возвращаемой строки: len(@UsedRows)-1, т.к. это всётаки выделение памяти:)

      Так же создание временной таблицы можно вынести за условие (ведь таблица нам нужна в любом случае).
      А данные в нее вставлять конструкцией INSERT ... SELECT. Это далеко не критично, но читать удобнее:)

      Огромное спасибо за скрипт:) Ибо я никогда на таком уровне не занимался запросами - он мне очень помог.

    • Продолжая тему: http://www.t-sql.ru/?p=489

    • все хорошо. :)

    • скрипт не работает.
      раньше был рабочий скрипт тут.



    Необходимо войти на сайт, чтобы оставлять комментарии

    Раздел FAQ: Microsoft SQL Server / Скрипты / T-SQL / Перекрестные запросы