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

Откуда:
Сообщений: 2083
Здравствуйте!
Есть ХП, что возвращает такой набор данных (для примера период Сентябрь 2012 - Февраль 2013)
НаименованиеГодМесяцКварталСумма
st1201293 квартал100
st2201293 квартал150
st12012104 квартал340
st22012104 квартал360
st12012114 квартал200
st22012114 квартал250
st12012124 квартал70
st22012124 квартал95
st1201311 квартал450
st2201311 квартал400
st1201321 квартал700
st2201321 квартал800

Требуется получить данные в развернутом виде:
Наименование93 квартал1011124 квартал121 квартал
st1100100340200706104507001150
st2150150360250957054008001200

Сложность в том, что надо в Pivot еще и кварталы приткнуть, а как - ума не приложу :(

declare @t table (naim varchar(50), yy int, mm int, qq varchar(50), summa int)
insert @t values('st1',2012,9,'3 квартал',100), ('st2',2012,9,'3 квартал',150), ('st1',2012,10,'4 квартал',340), ('st2',2012,10,'4 квартал',360),
('st1',2012,11,'4 квартал',200), ('st2',2012,11,'4 квартал',250), ('st1',2012,12,'4 квартал',70), ('st2',2012,12,'4 квартал',95),
('st1',2013,1,'1 квартал',450), ('st2',2013,1,'1 квартал',400), ('st1',2013,2,'1 квартал',700), ('st2',2013,2,'1 квартал',800)

select * from @t

select *
from @t
pivot (sum(summa) for mm in([9],[10],[11],[12],[1],[2])) pvt
order by yy,qq
28 мар 13, 14:41    [14107511]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
user89
Сложность в том, что надо в Pivot еще и кварталы приткнуть, а как - ума не приложу :(
select *,[10]+[11]+[12] as [4 квартал]...
28 мар 13, 14:49    [14107583]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
user89
Member

Откуда:
Сообщений: 2083
Cygapb-007,

не могу прикрутить к динамическому SQL. Периоды будут меняться, и в квартале будет разное количество месяцев.
Моя неудачная попытка:
if object_id('tempdb..#t') is not null drop table #t
create table #t (naim varchar(50), yy int, mm int, qq varchar(50), summa int)
insert #t values('st1',2012,9,'3 квартал',100), ('st2',2012,9,'3 квартал',150), ('st1',2012,10,'4 квартал',340), ('st2',2012,10,'4 квартал',360),
('st1',2012,11,'4 квартал',200), ('st2',2012,11,'4 квартал',250), ('st1',2012,12,'4 квартал',70), ('st2',2012,12,'4 квартал',95),
('st1',2013,1,'1 квартал',450), ('st2',2013,1,'1 квартал',400), ('st1',2013,2,'1 квартал',700), ('st2',2013,2,'1 квартал',800)
--select * from #t

declare @col varchar(max), @s varchar(max)
select @col = stuff((select ',[' + cast(mm as varchar(2)) +']' from (select distinct mm,yy from #t)t order by yy,mm for xml path('')), 1,1,'')

set @s = 'select naim, ' + @col + char(10) +
'from #t' + char(10) +
'pivot (sum(summa) for mm in(' + @col + ')) pvt'

print @s
exec (@s)

if object_id('tempdb..#t') is not null drop table #t
28 мар 13, 15:27    [14107934]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
Glory
Member

Откуда:
Сообщений: 104751
select *
from 
(select naim, yy, cast(mm as nvarchar(50)) as mm, summa 
from @t
UNION ALL
select naim, yy, qq, summa from @t) as x
pivot (sum(summa) for mm in([9],[10],[11],[12],[1],[2],[1 квартал],[2 квартал],[3 квартал],[4 квартал])) pvt
order by yy


user89
Периоды будут меняться, и в квартале будет разное количество месяцев

В смысле - разное ?
4 квартал вдруг начнет включать в себя месяцы с июня по ноябрь ?
28 мар 13, 15:41    [14108054]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
user89
Member

Откуда:
Сообщений: 2083
Glory,

имелось ввиду, что если период будет начинаться с сентября, то в 3 квартале будет 1 месяц ([9] as [3 квартал])
Если период начинается с июля, то в 3 квартале будет 3 месяца ([7]+[8]+[9] as [3 квартал])
Строчку типа [9],[10],[11] я получаю без проблем (написал в прошлом посте)
А теперь как-то надо получит еще и [7]+[8]+[9] as [3 квартал]

Также еще копаю (пока безуспешно) в таком направлении: получить столбец с месяцами в виде
9
3 квартал
10
11
12
4 квартал
1
2
1 квартал

Тогда Pivot отработает нормально.
28 мар 13, 15:52    [14108159]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
Glory
Member

Откуда:
Сообщений: 104751
user89
имелось ввиду, что если период будет начинаться с сентября, то в 3 квартале будет 1 месяц ([9] as [3 квартал])

И что с того ? число месяцев в квартале все равно остается таким же

select naim, yy, 
[1],[2],[3], ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0) as [1 квартал],
[4],[5],[6], ISNULL([4],0)+ISNULL([5],0)+ISNULL([6],0) as [2 квартал],
[7],[8],[9], ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0) as [3 квартал],
[10],[11],[12], ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0) as [4 квартал]
from 
@t

pivot (sum(summa) for mm in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvt
order by yy
28 мар 13, 16:00    [14108214]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
user89
Member

Откуда:
Сообщений: 2083
Glory,

Ваш запрос возвращает 6 строк вместо 2-х, но я знаю как потом "схлопнуть" лишние строки и убрать лишние столбцы.
Идею понял, спасибо
28 мар 13, 16:12    [14108338]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
declare @col varchar(max)
set  @col=stuff((select ','+QUOTENAME(qq) from (
   select distinct (yy*16+(mm+2)/3*4) s, qq from #t 
   union all 
   select distinct (yy*16+mm+(mm-1)/3) ym, convert(varchar,mm) from #t 
)u order by s for xml path('')),1,1,'')
select @col [@col]

select *
from (select naim,summa,convert(varchar,mm)mm from #t union all select naim,sum(summa),qq from #t group by naim,qq)t
pivot (sum(summa) for mm in([9],[3 квартал],[10],[11],[12],[4 квартал],[1],[2],[1 квартал])) pvt
28 мар 13, 16:13    [14108355]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
user89
Member

Откуда:
Сообщений: 2083
Cygapb-007,

очень хороший вариант. Большое спасибо!
28 мар 13, 16:30    [14108541]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
user89
Cygapb-007,

очень хороший вариант. Большое спасибо!
Плох тем, что 2 лишних скана таблицы. ПМСМ лучше в интерфейсе кварталы добавить, чем от сервера их получать, задваивая данные
28 мар 13, 16:32    [14108553]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
user89
Member

Откуда:
Сообщений: 2083
Источник данных - хранимая процедура. Максимальный период может быть 24 месяца и не превысит 1500 зап.
Её результат я кидаю во временную таблицу #t через
insert #t exec MyProcudure

И все манипуляции проводим именно с #t. Сейчас прикрутил Ваш алгоритм к реальным данным, двойной скан и pivot отрабатывают < 1 сек.
28 мар 13, 17:04    [14108794]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Нестандартный Pivot  [new]
Дарт Вейдер
Guest
user89,

declare @col varchar(max), @s varchar(max)

select naim, min(yy) [yy], case when grouping(mm) = 1 then min(qq) else cast(mm as varchar(2)) end [mm],
sum(summa) [summa], isnull(mm + ((mm-1)/3), left(min(qq),1)*4) [num]
into #tmp from #t
group by rollup(cast(yy as char(4))+qq, naim, mm)
having grouping(cast(yy as char(4))+qq) = 0 and grouping(naim) = 0

select @col = stuff((select ',' + quotename(mm) from #tmp group by yy,mm order by yy,min(num) for xml path('')), 1,1,'')

set @s = 'select * from (select naim, mm, summa from #tmp) t pivot (sum(summa) for mm in (' + @col + ')) pvt'
exec (@s)
29 мар 13, 18:31    [14114394]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить