none
pivot - заранее неизвестные периоды RRS feed

  • Вопрос

  • Привет всем! 

    Есть работающий запрос 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

    Как построить запрос, если количество периодов известно (шесть периодов), но значения периодов - нет?

    Желательно без динамического sql. Например, как можно жестко прописать MonthMin1, MonthMin2...MonthMin5, MonthMin6 ?

    13 августа 2012 г. 6:53

Ответы

Все ответы

  • Желательно без динамического sql. Например, как можно жестко прописать MonthMin1, MonthMin2...MonthMin5, MonthMin6 ?

    только динамика

    http://www.t-sql.ru

    13 августа 2012 г. 8:15
    Модератор
  •   То есть нужно написать табличную функцию? На oracle сделал бы сам, а здесь нуждаюсь в помощи. Если можно, подскажите синтаксис. 
    13 августа 2012 г. 9:06
  •   То есть нужно написать табличную функцию? На oracle сделал бы сам, а здесь нуждаюсь в помощи. Если можно, подскажите синтаксис. 

    http://t-sql.ru/post/Crosstab.aspx


    http://www.t-sql.ru

    • Помечено в качестве ответа Abolmasov Dmitry 23 августа 2012 г. 6:06
    13 августа 2012 г. 9:37
    Модератор
  • Табличная функция для фиксированных периодов работает. 
    Как здесь перечень [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 августа 2012 г. 13:53
  • Теперь остается вопрос по оптимизации кода. Как видно, у меня в зависимости от входящего параметра @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 августа 2012 г. 17:29
  • по ссылке, которую я дал, можно найти пример относительно гибкого решения для разных таблиц на входе и разных колонок


    http://www.t-sql.ru

    14 августа 2012 г. 4:19
    Модератор