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

Откуда:
Сообщений: 70
Как подойти к решению такой задачи:

(MS SQL 2005)

Есть таблица Date|Key|Value

create table #KeyValues (
rid int identity primary key,
InputDate datetime,
[Key] varchar(20),
Value int
);

Требуется сделать отчет, подсчитывающий сумму по полю Value в разрезе показателя Key, месяца и года.
Итоговых строк 12 по числу месяцев (данные есть по всем месяцам ), а вот столбцы должны состоять из показателя и года
и быть отсорированы по показателю + году.

Примерный вид отчета:

Month | Key1_Year1 | Key1_Year2 | ... | Key1_YearN | Key2_Year1 | Key2_Year2 | ... Key2_YearN | ... | KeyM_Year1 | KeyM_Year2 | ... | KeyM_YearN |
Jan |
Feb |
..
Dec |
8 авг 13, 17:23    [14682873]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это с использованием PIVOT  [new]
Shakill
Member

Откуда: мск
Сообщений: 1880
papa1, подсчитайте сначала "сумму по полю Value в разрезе показателя Key, месяца и года", а разворот в шахматку по годам делайте уже в клиентском приложении или отчетной системе, что там у вас
8 авг 13, 17:30    [14682921]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это с использованием PIVOT  [new]
papa1
Member

Откуда:
Сообщений: 70
Shakill,
спасибо!
8 авг 13, 17:33    [14682943]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это с использованием PIVOT  [new]
fqwefqwef
Guest
papa1,

если кинешь данные, сделаю запрос.
8 авг 13, 17:55    [14683066]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это с использованием PIVOT  [new]
ertherthrterth
Guest
fqwefqwef,

ridInputDateKeyValue
12011-01-21 00:00:00.000K13
22011-02-21 00:00:00.000K24
32011-05-21 00:00:00.000K18
42011-05-23 12:00:00.000K12


select r as [Month], K1_2011, K2_2011
from
(
	select [key] + N'_' + cast(year(inputdate) as varchar(4)) as c, datename(month,inputdate) as r, value
	from [dbo].[KeyValues]
	
) as d
pivot(sum(value) for c in (K1_2011, K2_2011)) as w


MonthK1_2011K2_2011
Май10NULL
ФевральNULL4
Январь3NULL
8 авг 13, 18:15    [14683133]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это с использованием PIVOT  [new]
papa1
Member

Откуда:
Сообщений: 70
ertherthrterth,
спасибо,
ваш примеру закрыл задачу (осталось динамику прикрутить для формирования столбцов)

+ пока промежуточный вариант без динамики такой

--drop table #KeyValues

create table #KeyValues (
rid int identity primary key,
InputDate datetime,
[Key] varchar(20),
Value int
);
insert into #KeyValues (InputDate,[Key],Value)
select '2011-01-21 00:00', 'K1',	3
union all select '2011-02-21 00:00', 'K2',	4
union all select '2011-05-21 00:00', 'K1',	8
union all select '2011-05-23 12:00', 'K1',	2
--
union all select '2012-01-21 00:00', 'K1',	30
union all select '2012-02-21 00:00', 'K2',	40
union all select '2012-05-21 00:00', 'K1',	80
union all select '2012-05-23 12:00', 'K1',	20
--
union all select '2013-01-21 00:00', 'K1',	300
union all select '2013-02-21 00:00', 'K2',	400
union all select '2013-05-21 00:00', 'K1',	800
union all select '2013-05-23 12:00', 'K1',	200


select datename(month,'2013'+r+'01') as [Month], K1_2011, K1_2012, K1_2013, K2_2011, K2_2012, K2_2013
from
(
	select [key] + N'_' + cast(year(inputdate) as varchar(4)) as c, right(convert(varchar(6),inputdate,112),2) as r, value
	from #KeyValues
	
) as d
pivot(sum(value) for c in (K1_2011, K1_2012, K1_2013, K2_2011, K2_2012, K2_2013)) as w
order by r

8 авг 13, 19:05    [14683271]     Ответить | Цитировать Сообщить модератору
 Re: Как сделать это с использованием PIVOT  [new]
Empirical
Member

Откуда:
Сообщений: 99
papa1
(осталось динамику прикрутить для формирования столбцов)


Наваяла на скорую руку, жду критики гур

alter procedure pivt(@query nvarchar(max)) as 
begin
declare @st nvarchar(max)
set @st = N'
declare @st1 nvarchar(max) 
declare @txt1 nvarchar(max)
set @st1 = ''''
create table #tempiv (rw varchar(max), col varchar(max), val real)
insert into #tempiv(rw,col,val) select rw, col, val from (' + @query + ') as t
DECLARE temp_cursor CURSOR FOR 
select distinct quotename(col) as col from #tempiv
OPEN temp_cursor
FETCH next from temp_cursor into @txt1
WHILE @@FETCH_STATUS = 0
   BEGIN
	  select @st1 = @st1 + @txt1 + '',''  
      FETCH next from temp_cursor into @txt1
   END;
CLOSE temp_cursor
DEALLOCATE temp_cursor
if (len(@st1)= 0) select ''no columns found for a pivot table'' 
else select @st1 = ''select rw,''+substring(@st1,1,len(@st1)-1)+''from (select * from #tempiv) as s pivot(sum(val) for col in (''+substring(@st1,1,len(@st1)-1)+'')) as p''
exec(@st1)
'
exec(@st)
end

exec pivt N'select cast(DATEPART(mm, InputDate) as varchar) as rw, [Key] + ''_'' + cast(DATEPART(yy, InputDate) as varchar) as col, Value as val from #KeyValues'
9 авг 13, 13:06    [14686516]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить