Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Привет всем!

Есть работающий запрос PIVOT, при условии если известно какие периоды выводить:

SELECT * FROM 
(SELECT     TOP (6) contractid, accountingDate2, restAmount2 FROM          
(SELECT     StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) 
AS accountingDate2, SUM(restAmount) AS restAmount2, COUNT(1) AS cnt
FROM         vpvbkiR_Record AS t
WHERE      (CONVERT(varchar, StatementID) = '135b8c93-0801-44e3-9dc8-319608') AND (contractid = 1)
GROUP BY StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6)) AS k
ORDER BY accountingDate2 DESC) AS h PIVOT (sum(restAmount2) 
FOR accountingDate2 IN ([201206], [201205], [201204], [201203], [201202], [201201])) AS S


Как построить запрос, если количество периодов известно, но значения периодов - нет?
10 авг 12, 21:02    [12996493]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
ecivgamer
Как построить запрос, если количество периодов известно, но значения периодов - нет?
Можно динамический SQL использовать
10 авг 12, 21:40    [12996576]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
alexeyvg, можно пример?
10 авг 12, 21:52    [12996609]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
NoLock
Member

Откуда: левый верний угол
Сообщений: 90
А для чего нужен этот запрос?
Если он используется в отчете (SSRS) - сделайте там группу по колонкам.
10 авг 12, 21:53    [12996611]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
NoLock, это отчет в fastreport. Должен выводиться текущий месяц, например [201208], и дальше еще 5 месяцев в порядке уменьшения.

А "группу по колонкам" - это как?
10 авг 12, 21:59    [12996625]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
ecivgamer
alexeyvg, можно пример?
Какой тут нужен пример?

Делаете строковую переменную, в неё пишете текст вашего запроса, но с нужными вам значениями accountingDate2.
Потом выполняете текст запроса, получившийся в этой переменной, командой exec(переменная)
10 авг 12, 22:02    [12996637]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
ecivgamer
NoLock, это отчет в fastreport. Должен выводиться текущий месяц, например [201208], и дальше еще 5 месяцев в порядке уменьшения.

А "группу по колонкам" - это как?
А, тогда нужно проще, без PIVOT

В любой системе для репортов есть средства для отображения таких таблиц, это собственно их главное назначение - отображать данные разными способами. Спросите на форуме по fastreport
10 авг 12, 22:05    [12996646]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
alexeyvg, в отчетной системе fastreport разве можно использовать exec ?
Я в sql server новичек, но выражаясь словами из oracle, по-моему здесь нужен чистый sql.

Вроде как вертится в мозгу мысля, что можно сгенерировать ряд для IN с помощью чего-то похожего на "between sysdate and sysdate - 180", только пока непонятно как это сделать в sql server.
10 авг 12, 22:09    [12996663]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
ecivgamer
alexeyvg, в отчетной системе fastreport разве можно использовать exec ?
Я в sql server новичек, но выражаясь словами из oracle, по-моему здесь нужен чистый sql.
Я в fastreport новичёк :-)

Но вообще по идее должно быть можно.

Но я вам говорил про то, что в fastreport не нужен этот PIVOT, он сам должен суметь нарисовать как надо на экранчике.
10 авг 12, 22:35    [12996779]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
londinium
Member

Откуда: Киев
Сообщений: 1176
автор
Должен выводиться текущий месяц, например [201208], и дальше еще 5 месяцев в порядке уменьшения.


Так если всегда ровно 5 месяцев от текущей даты, зачем здесь динамика? Жестко прописываете MonthMin1, MonthMin2...MonthMin5
10 авг 12, 23:21    [12996957]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

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

подскажите плз, как здесь жестко прописать MonthMin1, MonthMin2...MonthMin5 ?

SELECT     *
FROM         
(SELECT     TOP (6) contractid, accountingDate2, restAmount2
 FROM          
  (SELECT     StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) AS accountingDate2, 
   SUM(restAmount) AS restAmount2, COUNT(1) AS cnt
   FROM          vpvbkiR_Record AS t
   WHERE      (CONVERT(varchar, StatementID) = '135b8c93-0801-44e3-9dc8-319608') AND (contractid = 1)
   GROUP BY StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6)) AS k
   ORDER BY accountingDate2 DESC) 
AS h PIVOT (sum(restAmount2) FOR accountingDate2 IN ([201206], [201205], [201204], [201203], [201202], [201201])) AS S



пробовал вот так, но получаю везде null:

SELECT     *
FROM         
(SELECT     TOP (6) contractid, accountingDate2, restAmount2
 FROM          
  (SELECT StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) AS accountingDate2, 
   SUM(restAmount) AS restAmount2, COUNT(1) AS cnt
   FROM vpvbkiR_Record AS t
   WHERE      (CONVERT(varchar, StatementID) = '135b8c93-0801-44e3-9dc8-319608') AND (contractid = 1)
   GROUP BY StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6)) AS k
   ORDER BY accountingDate2 DESC) 
   AS h PIVOT (sum(restAmount2) FOR accountingDate2 IN ([mnth1], [mnth2], [mnth3], [mnth4], [mnth5], [mnth6])) AS S
13 авг 12, 10:45    [13002604]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Начинает получаться сгенерировать перечень периодов через запятую, только почему-то на выходе получаю список по возрастанию, а нужно по ниспадающей:

WITH data AS 
(SELECT     TOP (6) contractid, accountingDate2, restAmount2
 FROM         
  (SELECT     StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) AS accountingDate2, 
   SUM(restAmount) AS restAmount2, COUNT(1) AS cnt
   FROM vpvbkiR_Record AS t
   WHERE      (CONVERT(varchar, StatementID) = '135b8c93-0801-44e3-9dc8-319608') AND (contractid = 1)
   GROUP BY StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6)) AS k
   ORDER BY accountingDate2 DESC), data2 AS
    (SELECT     TOP (100) PERCENT accountingDate2
      FROM          data AS data_1
      ORDER BY accountingDate2 DESC)
       SELECT     ',' + accountingDate2
       FROM         data2 AS data_1
       GROUP BY accountingDate2 FOR XML PATH(''), TYPE


Как отсортировать в обратном порядке (ORDER BY accountingDate2 ASC - не помогает) ?
13 авг 12, 12:27    [13003181]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Насколько понимаю, без динамики здесь не обойтись. Подскажите плз синтаксис для создания табличной функции. На оракле сделал бы сам, а тут нуждаюсь в помощи...
13 авг 12, 13:15    [13003532]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33977
Блог
ecivgamer
Насколько понимаю, без динамики здесь не обойтись

При желании можно и обойтись, если использовать значения вида "текущий период", "текущий период - 1" и т.д.
13 авг 12, 13:19    [13003553]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Критик,

можно пример использования в запросе PIVOT значений "текущий период", "текущий период - 1" и т.д. ?
13 авг 12, 13:21    [13003566]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33977
Блог
create table #t (d date, f float)

insert into #t
select getdate(),  890 union all
select getdate()-55, 534 union all
select getdate()-30, 653 union all
select getdate()-44, 653


select 'Суммарный показатель' as Показатель, [текущий период],[прошлый период],[позапрошлый период]
from 
(select case
          when d = convert(date,getdate()) then 'текущий период'
          when d = convert(date,getdate()-55) then 'позапрошлый период'
          when d = convert(date,getdate()-30) then 'прошлый период'
          when d = convert(date,getdate()-44) then 'позапрошлый период'
        end as dt, f
  from #t) as SourceTable
PIVOT (sum(f) for dt in ([текущий период],[прошлый период],[позапрошлый период])) AS PivotTable;


понятно, что в реальном случаем равенство в case будет заменено на between-что-то-там
13 авг 12, 14:34    [13004122]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
ecivgamer, в FastReport есть Кросс-отчеты и вертикальные бэнды...Использовать можно любой, то что тебе нужно. Скачай документацию с сайта FastReport, там все это есть.
13 авг 12, 14:46    [13004212]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
MrBlackJack, меня терзают смутные сомненья насчет функциональности fastreport. Дело в том, что по заданию заказчика, периодов в таблице должно отображаться столько, сколько влезет (где-то 6 периодов с текущими размерами колонок), а дальше - продолжение в новой строке. По-моему, это реализуемо в табличной функции (sql server table-valued function), но не в функционале fastreport. А фастрепорту уже скормить готовый запрос к функции. Поправьте, если я ошибаюсь.
13 авг 12, 15:29    [13004581]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
MrBlackJack
Member

Откуда:
Сообщений: 43
ecivgamer
MrBlackJack, меня терзают смутные сомненья насчет функциональности fastreport. Дело в том, что по заданию заказчика, периодов в таблице должно отображаться столько, сколько влезет (где-то 6 периодов с текущими размерами колонок), а дальше - продолжение в новой строке. По-моему, это реализуемо в табличной функции (sql server table-valued function), но не в функционале fastreport. А фастрепорту уже скормить готовый запрос к функции. Поправьте, если я ошибаюсь.

В первом сообщение ты писал, что количество периодов известно, так?
ecivgamer
Дело в том, что по заданию заказчика, периодов в таблице должно отображаться столько, сколько влезет (где-то 6 периодов с текущими размерами колонок), а дальше - продолжение в новой строке.

В какой таблице? Какие размеры? Ты имеешь ввиду размеры колонок на листе FastReport? Я уже не понимаю, что ты имеешь ввиду...
13 авг 12, 15:54    [13004747]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

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

В первом сообщение ты писал, что количество периодов известно, так?


Известно сколько нужно вывести периодов в одной строке - так как больше не влезет на страницу.
Неизвестно, сколько периодов суммарно.

В какой таблице? Какие размеры? Ты имеешь ввиду размеры колонок на листе FastReport?


Да, это размеры колонок на листе FastReport.

Извините, что туманно объясняюсь.

зы: Тем временем сейчас учусь выводить из табличной функции разные периоды в зависимости от входящего параметра...
13 авг 12, 16:01    [13004799]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Не дело заниматься оформлением данных на скуле.
Скуль должен вывести данные в формате удобные скулю, а пивоты, агрегирование, группировка, сортировка, декорирование - это удел клиента.

То, что было выбрано неправильные клиентские инструменты - проблемы архитектора, а не прогера на скуле.
Тот же Exel не имеет проблем ни с пивотом ни с чем подобным.

Это я не говорю про OLAP.

PS: Используй FastCube, Люк
13 авг 12, 16:33    [13005041]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Mnior, на вопрос к вышестоящему руководству "почему выбран fastreport" получил ответ - "он дешевле".
Вот и занимаюсь акробатикой.
13 авг 12, 16:58    [13005202]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Табличная функция для фиксированных периодов работает.
Как здесь перечень [201206], [201205], [201204], [201203], [201202], [201201] сделать динамическим типа month1, month2..., month6 ?

DROP FUNCTION dbo.ufn_SalesByStore5;
GO
CREATE FUNCTION ufn_SalesByStore5 (@storeid int)

RETURNS @Result TABLE

(
	contractid int, [201206] int, [201205] int, [201204] int, [201203] int, [201202] int, [201201] int
)

AS
BEGIN

IF @storeid = 1 
    insert into @Result 
SELECT     *
FROM         
(SELECT     TOP (6) contractid, accountingDate2, restAmount2
 FROM          
 (SELECT     contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) AS accountingDate2, 
  restAmount2 AS restAmount2
  FROM          testdata AS t
  WHERE      (CONVERT(varchar, StatementID) = '135b8c93-0801-44e3-9dc8-319608') AND (contractid = 1)
  --GROUP BY StatementID, contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6)
) AS k
  ORDER BY accountingDate2 DESC) AS h 
  PIVOT (sum(restAmount2) FOR accountingDate2 IN ([201206], [201205], [201204], [201203], [201202], [201201])) AS S

ELSE IF @storeid = 2 
--under construction
    insert into @Result 
select 0 as contractid, 0 as [201206], 0 as [201205], 0 as [201204], 0 as [201203], 0 as [201202], 0 as [201201]

RETURN 
END

GO
13 авг 12, 17:52    [13005552]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
сорри, Критик ведь мне уже показал пример с case...
13 авг 12, 17:54    [13005571]     Ответить | Цитировать Сообщить модератору
 Re: pivot - заранее неизвестные периоды  [new]
ecivgamer
Member

Откуда:
Сообщений: 692
Теперь остается вопрос по оптимизации кода. Как видно, у меня в зависимости от входящего параметра @storeid выбирается один из двух селектов - транспонирование по разным показателям (колонкам). На деле таких селектов должно быть еще несколько. Возможно ли в SQL Server 2005 каким-то образом в табличной функции в зависимости от входящего параметра указать колонку, по которой нужно транспонировать?

DROP FUNCTION dbo.pvbki_transpose;
GO
CREATE FUNCTION pvbki_transpose (@storeid int, @StatementID varchar(36), @contractid int, @rowbeg int, @rowend int)

RETURNS @Result TABLE
(contractid int, [1] int, [2] int, [3] int, [4] int, [5] int, [6] int)

AS
BEGIN

IF @storeid = 1 
    insert into @Result 
SELECT     contractid, [1], [2], [3], [4], [5], [6]
FROM         
(SELECT     TOP (6) contractid, restAmount2, rankd
 FROM (select rank() OVER (ORDER BY SUBSTRING(CONVERT(varchar, accountingDate2, 112), 1, 6) desc) as rankd, 
  contractid, accountingDate2, restAmount2 from 
 (SELECT rank() OVER (ORDER BY SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) desc) as ranks,
  contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) AS accountingDate2, 
  restAmount2 AS restAmount2
  FROM          testdata AS t
  WHERE      (CONVERT(varchar, StatementID) = rtrim(@StatementID)) AND (contractid = @contractid) 
) AS k where (ranks between @rowbeg and @rowend)
  ) AS h ) as j
  PIVOT (sum(restAmount2) FOR rankd IN ([1], [2], [3], [4], [5], [6])) AS S

ELSE IF @storeid = 2 
    insert into @Result 
SELECT     contractid, [1], [2], [3], [4], [5], [6]
FROM         
(SELECT     TOP (6) contractid, accountingDate2, rankd
 FROM (select rank() OVER (ORDER BY SUBSTRING(CONVERT(varchar, accountingDate2, 112), 1, 6) desc) as rankd, 
  contractid, accountingDate2, restAmount2 from
 (SELECT rank() OVER (ORDER BY SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) desc) as ranks,
  contractid, SUBSTRING(CONVERT(varchar, accountingDate, 112), 1, 6) AS accountingDate2, 
  restAmount2 AS restAmount2
  FROM          testdata AS t
  WHERE      (CONVERT(varchar, StatementID) = rtrim(@StatementID)) AND (contractid = @contractid)
) AS k where (ranks between @rowbeg and @rowend)
  ) AS h ) as j
  PIVOT (max(accountingDate2) FOR rankd IN ([1], [2], [3], [4], [5], [6])) AS S

RETURN 
END

GO
13 авг 12, 21:24    [13006259]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить