Хранимая процедура crosstab - аналог TRANSFORM MS ACCESS

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

Актуальной в последнее время становится задача преобразования разработанных ранее mdb приложений MS ACCESS в ADP проекты. Одна из сложностей такого преобразования состоит в том, что не все инструкции языка SQL MS ACCESS поддерживаются в MS SQL.

Часто встречается инструкция TRANSFORM, создающая 'перекрестный' набор данных. Аналога в MS SQL для этой инструкции нет.

Предлагается Хранимая процедура, решающая эту проблему.

CREATE PROCEDURE crosstab 
@source varchar(1000), --то, что между FROM и WHERE
@fields varchar(100)=null, --поля для отбора... по ним происходит группировка. Если не заданы, то результатом будет одна строка - итог по всем записям
@func varchar(100), -- функция вида sum(some_field), count... и так далее ... НЕДОПУСТИМО использовать в качестве аргумента * (звездочку)
@pivot varchar(1000), -- правило для формирования полей
@filtr varchar(1000)=null, -- Условия отбора (то, что в запросе следует после WHERE)
@into varchar(100)=null -- Таблица, которая будет создана и заполнена результатами выполнения процедуры (опционально)

AS

DECLARE 
@select VARCHAR(8000), 
@sql VARCHAR(8000), 
@sql1 VARCHAR(8000), 
@sql2 VARCHAR(8000), 
@sql3 VARCHAR(8000), 
@sql4 VARCHAR(8000), 
@sql5 VARCHAR(8000), 
@sql6 VARCHAR(8000), 
@sql7 VARCHAR(8000), 
@sql8 VARCHAR(8000), 
@sql9 VARCHAR(8000), 
@sqlend VARCHAR(8000), 
@delim VARCHAR(1), 
@sqllen numeric(10), 
@sqlnum numeric(10), 
@pvalue VARCHAR(100),
@filtr1 varchar(1000) 

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SET @filtr1=isnull(' AND '+@filtr, '')
EXEC ('SELECT DISTINCT ' + @pivot + ' as pivot INTO ##pivot FROM ' + @source + ' WHERE ' 
+ @pivot + ' Is Not Null '+ @filtr1)

SELECT @sql='',
@sql1='',
@sql2='',
@sql3='',
@sql4='',
@sql5='',
@sql6='',
@sql7='',
@sql8='',
@sql9='',
@select='SELECT '+isnull(@fields+',',''),  
@func=stuff(@func, len(@func), 1, ' END)')


SELECT top 1 @delim = CASE ISNUMERIC(pivot) WHEN 1 THEN '' ELSE '''' END FROM ##pivot
DECLARE cur_pivot_cursor scroll CURSOR FOR select pivot from ##pivot order by pivot  

OPEN cur_pivot_cursor

FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

SET @sqlnum = 1

WHILE @@FETCH_STATUS = 0

BEGIN

-- итерация складывания полей
SET @sql=@sql + '''' + convert(VARCHAR(100), @pvalue) + ''' = ' + 
stuff(@func,charindex( '(', @func )+1, 0, 'CASE ' + @pivot + 
' WHEN ' + @delim + convert(VARCHAR(100), @pvalue) + @delim + ' THEN ' ) + ', '

-- измеряем длину строки
SET @sqllen = len(@sql)

-- проверяем длину строки, если размер превышен, 
-- 1-й проход
IF @sqllen > 7800 and @sqlnum = 1 
BEGIN
SET @sql1 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 2-й проход
IF @sqllen > 7800 and @sqlnum = 2
BEGIN
SET @sql2 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 3-й проход
IF @sqllen > 7800 and @sqlnum = 3
BEGIN
SET @sql3 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 4-й проход
IF @sqllen > 7800 and @sqlnum = 4
BEGIN
SET @sql4 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 5-й проход
IF @sqllen > 7800 and @sqlnum = 5 
BEGIN
SET @sql5 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 6-й проход
IF @sqllen > 7800 and @sqlnum = 6
BEGIN
SET @sql6 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 7-й проход
IF @sqllen > 7800 and @sqlnum = 7
BEGIN
SET @sql7 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END

-- 8-й проход
IF @sqllen > 7800 and @sqlnum = 8
BEGIN
SET @sql8 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END 

-- 9-й проход
IF @sqllen > 7800 and @sqlnum = 9
BEGIN
SET @sql9 = @sql
SET @sqlnum = @sqlnum + 1
SET @sql = ''
SET @sqllen = 0
END 

-- получаем следующую строку
FETCH NEXT FROM cur_pivot_cursor
INTO @pvalue

END

-- курсор больше не нужен
CLOSE cur_pivot_cursor
DEALLOCATE cur_pivot_cursor

DROP TABLE ##pivot

-- убираем лишнюю запятую в конце всей серии срок
IF len(@sql) > 0
SELECT @sql=left(@sql, len(@sql)-1)
ELSE 
BEGIN
IF LEN(@sql9)>0
SELECT @sql9=left(@sql9, len(@sql9)-1)
ELSE IF LEN(@sql8)>0
SELECT @sql8=left(@sql8, len(@sql9)-1)
ELSE IF LEN(@sql7)>0
SELECT @sql7=left(@sql7, len(@sql7)-1)
ELSE IF LEN(@sql6)>0
SELECT @sql6=left(@sql6, len(@sql6)-1)
ELSE IF LEN(@sql5)>0
SELECT @sql5=left(@sql5, len(@sql5)-1)
ELSE IF LEN(@sql4)>0
SELECT @sql4=left(@sql4, len(@sql4)-1)
ELSE IF LEN(@sql3)>0
SELECT @sql3=left(@sql3, len(@sql3)-1)
ELSE IF LEN(@sql2)>0
SELECT @sql2=left(@sql2, len(@sql2)-1)
ELSE IF LEN(@sql1)>0
SELECT @sql1=left(@sql1, len(@sql1)-1)
END
SET @filtr1=isnull(' WHERE '+@filtr, '')
set @sqlend=isnull(' INTO '+@into,'')+' FROM '+@source+@filtr1+isnull(' GROUP BY '+@fields,'')

EXEC (@select+@sql1+@sql2+@sql3+@sql4+@sql5+@sql6+@sql7+@sql8+@sql9+@sql+@sqlend)
SET ANSI_WARNINGS ON
GO

Эта процедура создает строку запроса и выполняет ее командой EXEC.
Максимальная длина этого запроса около 85 килобайт.

Выполнить процедуру можно примерно так:

EXEC crosstab
'your_table_name1 a INNER JOIN your_table_name2 b ON a.id=b.id',
'a.field1, b.field2', 
'count(a.id)', 
'right(left(convert(varchar,b.datetime_field,121),13),2)', 
'b.datetime_field between ''20040701'' and getutcdate() and a.id > 1000', 
'##temp'

Описание входных параметров есть выше.