Опубликовано: 15 окт 04
Рейтинг:
Рейтинг:
Автор: 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'
Описание входных параметров есть выше.
Комментарии
вот посмотрите, забацал вариант
sql.ru/forum/actualthread.aspx?tid=642434
ведь компилятор (наверное в режиме по умолчанию? другого не встречал) на MS SQL 2005 не даст такое откомпилировать! Поле pivot формируется динамически, а используется статически.
В общем, наверное это таоке дело допускается на каких то версиях MS SQL?
А нельзя ли было написать без этой пурги?
здесь хоть кто-нибудь проверял этот запрос? Особенно те, кто похвалил, что она работает :)))))
ВО НАСМЕШИЛИ!!!
И что, тут на sql.ru все такое?
УЖАС!!!
Что за пурга?
Вы посмотрите хотя бы на эти две строки:
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
Что у вас за такое поле pivot ???? Вы что??? Изначально процедура писалась так, чтобы это поле было неизвестным для процедуры, а тут вдруг конкретное поле с именем pivot!
ЛАЖА!
этописцец... такой тормосяки еще невидел...
автору - учить SQL !!! - уже давно есть и покультурней и пошустрей...
>Ета пурга не работает (((
Работает на раз!!!
Сенкс , - день работы точно съэкономил!
EXEC crosstab 'va', 'IDAnalizDoc', 'count(IDAnalizDoc)', '', '', 'tva'
Результат:
Server: Msg 208, Level 16, State 1, Procedure crosstab, Line 54
Invalid object name '##pivot'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.
---------
Ета пурга не работает (((
версия cross-tab от ицик бен гана.
вещь!
http://www.softmatics.ru/sql/10.htm
Идите нафиг... Четал.... ниасилил......
##pivot и ##temp - это типа глобальные временные таблицы??? Автор - опомнись!
Это конечно хорошо посмотрите Ицик Бен Ган как это сделал!
отличная процедура
главное что оно работает!
Очень помогли. Спасибо.
Фантазия не-человеческой мысли... Среди нас инопланетяне!
Очень полезная процедура