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

Откуда: Москва
Сообщений: 26
Довелось столкнуться с формированием отчета в виде сводной таблицы, толкового примера нигде не нашел.
стандартный pivot не позволяет (или я не нашел) "рулить" порядком выводимых столбцов и требует заранее указать названия колонок.

в то время, как отчет формируется динамически.

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

могу сразу сказать, какие есть в настоящее время недоработки:
у меня используется для "разворачивания" ответов клиента на вопросы. Текст вопроса в заголовке колонки, клиент в заголовке строки, ответ клиента на пересечении. Но существуют вопросы типа "многозначный" - из списка вариантов ответа выбрать галками несколько. т.е. в запросе, возвращающем данные будет две строки с одинаковым названием колонки (текст вопроса), названием строки (клиентом), приоритетом, но с разными ответами (содержимым ячейки на пересечении).
моя процедура вернет в этом случае один из вариантов (выберет максимальную строку) - это не катит.

надо бы допилить и сцепить все варианты ответов через разделитель (например, запятую) или сделать две строки (в этом случае логика будет еще геморнее, особенно в случае нескольких многозначных ответов)

особое внимание обащаю на: используется динамический sql! причем текст запроса в качестве входного параметра процедуры!!! огромная брешь в безопасности! Динамика внутри хранимки также не есть хорошо, но хотя бы от пользовательских данных не вполне зависит.
Именно поэтому у меня в рабочей системе именно эта процедура и не используется.

В общем, готов к замечаниям и предложениям

К сообщению приложен файл (сводная таблица.sql - 1Kb) cкачать
20 апр 12, 12:10    [12444663]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
http://am.rusimport.ru/MsAccess/topic.aspx?id=285

http://t-sql.ru/post/Crosstab.aspx
20 апр 12, 12:27    [12444821]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 26
Knyazev Alexey,

Собственно говоря, я это видел, но допиливал.
Ибо в этих примерах нет возможности:
1. рулить порядком вопросов.
2. у меня возможны почти одинаковые вопросы ( "Довольны ли вы системой заказов?", "А почему?", "Довольны ли вы службой доставки?", "А почему?"), т.е. второй и четвертый вопросы звучат одинаково, но смысл у них именно разный!
3. В этих примерах данные собираются изо всей таблицы целиком, а это крайне редко нужно! Обычно именно надо "свести" результаты запроса.

Для решения я ввел дополнительно поле приоритета вопроса.

Однако, как я говорил в первом посте, в моей процедуре не решен вопрос с многозначными вопросами. грубо говоря вопрос звучит как:
"Почему вы выбрали нашу компанию?"

Прелагаются варианты ответов:
"Низкие цены"
"Сроки доставки"
"Качество обработки кол-центром"
"Достоверность и объем информации о товаре"

Клиент выбрал два! варианта ответа.
В запросе, который подготовит данные (и который я не привожу, ибо он сильно специфический для моей БД) будут две строки от одного клиента на один и тот же вопрос.
В моей процедуре на пересечении будет только один ответ - выбранный агрегатной функцией MAX. Это не вполне отвечает требованиям бизнеса - надо бы сцепить все ответы через разделитель-запятую. или сделать несколько строк на одного клиента, но это сильно геморно с моей точки зрения, да и просматривать так же не вполне удобно.

Наверное надо именно сцеплять. Сейчас этого нет, как сделать в принципе, представляю. чуть позже и это допилю.
20 апр 12, 12:50    [12445016]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 26
Допилил я свою процедурину и сгенерил скрипт для проверки ее работы.

EXEC [dbo].[sp_Pivot]
@Query='
set nocount on
declare @t table(
Customer varchar(max),
QstName varchar(127),
AnsTxt varchar(max),
QstPriority int
)

insert @t values(''Заказчик 1'', ''Однозначный вопрос 1'', ''Ответ 1 на Однозначный вопрос 1'',1)
insert @t values(''Заказчик 1'', ''Однозначный вопрос 2'', ''Ответ 1 на Однозначный вопрос 2'',2)
insert @t values(''Заказчик 1'', ''Многозначный вопрос 1'', ''Ответ 1 на Многозначный вопрос 1'',3)
insert @t values(''Заказчик 1'', ''Многозначный вопрос 1'', ''Ответ 2 на Многозначный вопрос 1'',3)

insert @t values(''Заказчик 2'', ''Однозначный вопрос 1'', ''Ответ 1 на Однозначный вопрос 1'',1)
--insert @t values(''Заказчик 2'', ''Однозначный вопрос 2'', ''Ответ 1 на Однозначный вопрос 2'',2)
--insert @t values(''Заказчик 2'', ''Многозначный вопрос 1'', ''Ответ 1 на Многозначный вопрос 1'',3)
insert @t values(''Заказчик 2'', ''Многозначный вопрос 1'', ''Ответ 2 на Многозначный вопрос 1'',3)


set nocount off
select * from @t
'
,@NameFirstCol='Заказчик'

К сообщению приложен файл (сводная таблица.sql - 3Kb) cкачать
20 апр 12, 14:37    [12445959]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Dorina
Guest
Девиченский Василий,

Очень полезная процедура!
У меня почему-то обрезается последний символ в каждой ячейке внутри таблицы, если данные в ячейке идут через разделитель.
И столбцы не группируются, т.е названия повторяются из раза в раз.

Пожалуйста, подскажите, как это исправить!
3 июн 12, 23:11    [12659118]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Dorina
Guest
С пропавшем последним символом вроде разобралась, но вот со столбцами проблема у меня...
3 июн 12, 23:29    [12659189]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Dorina
Guest
Все, вроде вопрос снимается, получилось!
Шикарная процедура, СПАСИБО!

P.S. извиняюсь з а3 сообщения подряд, надо было залогинится...
3 июн 12, 23:38    [12659222]     Ответить | Цитировать Сообщить модератору
 Re: Сводная таблица (аналог Pivot)  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 26
Dorina,

Спасибо за отзыв, рад, что помог. Но аккуратнее с динамическим sql.
Ибо параметры соединения с БД выдергиваются из клиентского приложения на счет раз простейшими средствами. Права на вызов хранимки у пользователя (который прописан в строке соединения) есть.
Т.е. злоумышленник выдергивает параметры соединения с БД, запускает management studio и соединяется с этими самыми правами.
но в тексте запроса пишет в середине 'drop database', например. А может и еще какую пакость.

тем самым получается беда! Поэтому именно эту хранимку в бою использовать настоятельно не рекомендую!
25 июл 12, 11:23    [12912612]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить