none
Tabela Dinâmica - Não é o que parece (eu acho..rs) RRS feed

  • Pergunta

  •  

    Boa noite a Todos,

     

    Eu sei que já devem estar pensando: 'Cara, existe um monte de post sobre isso!!'..rsrs...concordo, mas nenhum com o que eu estou tentando fazer, e é o seguinte:

     

    Tenho uma tabela, onde insiro valores e a data em que ela foi inserida;

    Preciso criar uma tabela com os ultimos 4 anos, onde as colunas tem que ter os nome desses 4 ultimos anos, ou seja, o nome delas devem ser dinâmicas, exemplo prático:

     

    2008, 2007, 2006, 2005...deverão ser os nomes da minha colunas, e ano que vem...2009, 2008, 2007, 2006...e assim por diante.....

     

    Isso é possível fazer??

     

    At's!

     

    quinta-feira, 27 de março de 2008 01:46

Respostas

  •  

    Cara o que vc. procura e fazer deve ser feito usando uma ferramenta de BI ( que e o mais adequado ), assim vc. transforma as linhas em colunas, sem problemas por exemplo o analisys services e a ferramenta do sql server para isso simples de usar e criada para trabalhar com dados da maneira que vc. precisa, uma outra ideia e no sql 2005 usar a funcao pivot mais vc. precisa saber quais os anos vc. quer "pivotear", entao fiz esta rotina que transforma as linhas em colunas de maneira dinamica, na pratica a performance nao e boa mais ajuda quando precisamos trazer nest tipo de formato segue:

     

    -- Esta funcao e para retornar somente o dia 01 de todos os meses
    CREATE FUNCTION fn_RBMS_ReturnMonth (@Vdata as Datetime)
    RETURNS DateTime
    AS
    BEGIN
    return cast(Str(month(@Vdata)) + '/01/'+ ltrim(Str(year(@Vdata))) as SMALLDateTime)
    END
    -- -- Cria uma Tabela de Exemplo
    Create Table #Dados (Idx int identity(1,1), Valor Numeric (19,2), Data DateTime)
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-02-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-04-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-05-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-02')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-05-07')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-08')

    Create Table #PivotTabela (CampoChave Int)
    Insert into #PivotTabela (CampoChave) Values (1) -- So para criar uma linha em branco
    Declare @DataI Datetime -- Sao Variaveis para fazer o loop
    Declare @DataF Datetime --
    Select @DataI = Dbo.fn_RBMS_ReturnMonth(Min(Data)), @DataF = Dbo.fn_RBMS_ReturnMonth(Max(Data))
    From #Dados

    -- Alimenta com a menor e a maior data para definir o range
    Declare @TotalMes Numeric(19,2)
    Declare @StringDDL Varchar(1000)
    While @DataI <= @DataF
    Begin
    Select @TotalMes = Sum(Valor) From #Dados
    Where Dbo.fn_RBMS_ReturnMonth(Data) = @DataI
    If @TotalMes > 0
    Begin
    Set @StringDDL = 'Alter Table #PivotTabela Add [' + Right(Convert(Char(10),@DataI,105),7) + '] Numeric(19,2) '
    Exec(@StringDDL)
    Set @StringDDL = 'Update #PivotTabela Set [' + Right(Convert(Char(10),@DataI,105),7) + '] = ' + convert(varchar(30),@Totalmes)
    Exec(@StringDDL)
    Set @TotalMes = 0
    End
    Set @DataI = DateAdd(m,1, @DataI)
    End

    Select * From #PivotTabela

     

    qq retorne.

     

    Avs;

    quinta-feira, 27 de março de 2008 10:38

Todas as Respostas

  •  

    Cara o que vc. procura e fazer deve ser feito usando uma ferramenta de BI ( que e o mais adequado ), assim vc. transforma as linhas em colunas, sem problemas por exemplo o analisys services e a ferramenta do sql server para isso simples de usar e criada para trabalhar com dados da maneira que vc. precisa, uma outra ideia e no sql 2005 usar a funcao pivot mais vc. precisa saber quais os anos vc. quer "pivotear", entao fiz esta rotina que transforma as linhas em colunas de maneira dinamica, na pratica a performance nao e boa mais ajuda quando precisamos trazer nest tipo de formato segue:

     

    -- Esta funcao e para retornar somente o dia 01 de todos os meses
    CREATE FUNCTION fn_RBMS_ReturnMonth (@Vdata as Datetime)
    RETURNS DateTime
    AS
    BEGIN
    return cast(Str(month(@Vdata)) + '/01/'+ ltrim(Str(year(@Vdata))) as SMALLDateTime)
    END
    -- -- Cria uma Tabela de Exemplo
    Create Table #Dados (Idx int identity(1,1), Valor Numeric (19,2), Data DateTime)
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-02-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-04-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-05-01')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-02')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-05-07')
    Insert Into #Dados (Valor, Data) Values (150.10, '2004-01-08')

    Create Table #PivotTabela (CampoChave Int)
    Insert into #PivotTabela (CampoChave) Values (1) -- So para criar uma linha em branco
    Declare @DataI Datetime -- Sao Variaveis para fazer o loop
    Declare @DataF Datetime --
    Select @DataI = Dbo.fn_RBMS_ReturnMonth(Min(Data)), @DataF = Dbo.fn_RBMS_ReturnMonth(Max(Data))
    From #Dados

    -- Alimenta com a menor e a maior data para definir o range
    Declare @TotalMes Numeric(19,2)
    Declare @StringDDL Varchar(1000)
    While @DataI <= @DataF
    Begin
    Select @TotalMes = Sum(Valor) From #Dados
    Where Dbo.fn_RBMS_ReturnMonth(Data) = @DataI
    If @TotalMes > 0
    Begin
    Set @StringDDL = 'Alter Table #PivotTabela Add [' + Right(Convert(Char(10),@DataI,105),7) + '] Numeric(19,2) '
    Exec(@StringDDL)
    Set @StringDDL = 'Update #PivotTabela Set [' + Right(Convert(Char(10),@DataI,105),7) + '] = ' + convert(varchar(30),@Totalmes)
    Exec(@StringDDL)
    Set @TotalMes = 0
    End
    Set @DataI = DateAdd(m,1, @DataI)
    End

    Select * From #PivotTabela

     

    qq retorne.

     

    Avs;

    quinta-feira, 27 de março de 2008 10:38
  • Show dela pelota...rs

     

    Eu posso usar criar ela dentro de uma procedure??

     

    At's!

    quinta-feira, 27 de março de 2008 11:14
  • sim sem prbolemas esse foi apenas um exemplo, vc. vai ter que adpatar a suas necessidades, mais como eu disse nao espere uma performance boa nao ...

     

    Abs;

     

    quinta-feira, 27 de março de 2008 11:16
  • Tudo bem, estou fazendo assim por que não achei no Analisys Services..rs..E estou terminando de adaptar, ja te retorno o resultado :>

    quinta-feira, 27 de março de 2008 12:06
  • Infelizmente não tenho a visão completa do seu problema, mas se você de fato está utilizando o Analysis Services, me parece que o mais apropriado seria criar um named set (que nada mais é que uma seleção de membros de uma dimensão, definida no servidor, e que pode ser dinâmica) para seu cubo. Named sets muito comuns: Últimos "n" Ano/mes/etc., Top 'n' produtos, etc.

     

    quinta-feira, 27 de março de 2008 14:45
  • Bom cara, agradeço a ajuda, mas eu não sei nem aonde eu acho esse cara, ja procurei no sql, no menu iniciar, e nada..rsr.

    quinta-feira, 27 de março de 2008 15:13
  • Funcionou cara, muito obrigado mesmo, agora ficou uma dúvida no ar...

    Por que esse tipo de prática tem queda de performance??

    quinta-feira, 27 de março de 2008 23:25